Extract a list from a table in Excel-CodePudding

If I set up an Excel table like so:

CodePudding user response：

With Microsoft 365 you can try-

=FILTER(A2:A5,MMULT(--(B2:E5="X")*(B1:E1=G1),SEQUENCE(COLUMNS(B2:E2))))

If you want to show result in single cell then use TEXTJOIN() function

=TEXTJOIN(", ",TRUE,FILTER(A2:A5,MMULT(--(B2:E5="X")*(B1:E1=G1),SEQUENCE(COLUMNS(B2:E2)))))

If your version of excel support BYROW() then could try-

=FILTER(A2:A5,BYROW(B2:E5,LAMBDA(a,SUM(--(a="X")*(B1:E1=G1)))))

CodePudding user response：

Just use FILTER twice:

=FILTER(A2:A5,FILTER(B2:E5,B1:E1=G1)="X")