google-sheetsgoogle-sheets-formula

Using FILTER with multiple conditions on Google Sheet

                    3 (D1)
        1   2   3   
Jane    O   a   a
Jack    a   O/I O
John    I   O   O

I have this tables. I want to use the cell value D1 (can be 1, 2, 3) to find the column to look up the value of O or O/I and return all the names.

Like

1    2    3
Jane Jack Jane
     John Jack

=FILTER(A2:A3, (INDEX(B2:D3, , D1) = "O") + (INDEX(B2:D3, , D1) = "O/I"))

I got to this far but Only returns 'Jane'. What should I add in order to get the desire result on Google Sheet?

enter image description here

Solution

  • Using Let to do a filter

    Try this

    =CHOOSECOLS(LET(x, A3:A5, xx, BYROW(CHOOSECOLS(B3:D5,D1), LAMBDA(r, REGEXMATCH(JOIN(",", r),"O"))), FILTER(HSTACK(x,xx), xx=TRUE)),1)