Excel Formulas FILTER, SEQUENCE, TEXTJOIN, TRANSPOSE | Excel

 

Filter using the formula, instead of lookup.

=FILTER(OUTPUT, CONDITION)

EXAMPLE: 

FILTER(A1:A10, B1:B10=C1)

OUTPUT WILL BE SPILLED DOWN. 

TRANSPOSE to spill this output in columns, instead of down in the rows.

=TRANSPOSE( FILTER(OUTPUT, CONDITION) )

EXAMPLE: 

=TRANSPOSE( FILTER(A1:A10, B1:B10=C1) )

TEXTJOIN for a spill in one cell with a separator like a comma.

=TEXTJOIN(",", FILTER(OUTPUT, CONDITION) )

EXAMPLE: 

=TEXTJOIN(",", FILTER(A1:A10, B1:B10=C1) )

SEQUENCE:

to have a list of numbers 

=SEQUENCE(row, column, start, interval)

example: 

1 row and 10 columns, left to right

=SEQUENCE(1,10)

1 column and ten rows, top to down, 

=SEQUENCE(10,1)

1 column and ten rows, top to down, starting from 5 and the interval is 2

=SEQUENCE(10,1, 5,2)