How to Ignore Blanks-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

Try this on cell E2:

=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)
)

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.