How to Ignore Blanks

Question

Hi, I got the solution with helper column. Can I get answer without helper column as shown in the picture. Thanks in advance..

Answer 1

Try this on cell E2, using LET for easy reading of the expression:

=LET(teams, A2:A5, names, B2:B5, dropDownValue, D2,
  helper, SCAN("", teams, LAMBDA(acc,tt, IF(acc="", tt, IF(tt="", acc, tt)))),
  FILTER(names, helper=dropDownValue)
)

or just using the ranges:

=FILTER(B2:B5,SCAN("",A2:A5,LAMBDA(acc,tt,IF(acc="",tt,IF(tt="",acc,tt))))=D2)

the idea is just to create the helper column on the fly via SCAN function. The rest is just to use FILTER function based on the drop-down value in cell D2. Here is the output:

Note: Based on your sample data, it is assumed the first value of teams column is non-empty and with the color value.

Answer 2

Use SCAN() function with FILTER().

=FILTER(D6:D17,SCAN("",C6:C17,LAMBDA(a,b,IF(b="",a&b,b)))=G6)

• Here SCAN() will generate an array filling empty cells with value of its above cell. Then just filter D column based on that array.