How can I use filter function like =FILTER(range/table name as ARRAY, cell reference as INCLUDE=1,&q-CodePudding

How can I use filter function like [=FILTER(Table1,D18=1,"")} in detail =FILTER(range/table name as ARRAY, cell reference as INCLUDE=1,"") (=FILTER(Table1,D18=1,as given in the snapshot below:

snapshot

CodePudding user response：

Try something along the lines of using XLOOKUP() & FILTER() Function

• Formula used in cell D19

=FILTER(A2:A16,XLOOKUP(D18,B1:P1,B2:P16)=1)

If you need without blanks or 0's then,

=FILTER(A2:A16,XLOOKUP(D18,B1:P1,B2:P16)<>0)

EDIT

As per comments of OP

• Formula used in cell G19

=LET(_x,XLOOKUP(G18,B1:P1,B2:P16),FILTER(HSTACK(A2:A16,_x),_x<>0))

CodePudding user response：

Exactly!, filter the table where the header equals the value in D18 and the values in given column equal 1, or should it filter to not empty.