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)
