Is it possible to filter horizontally?-CodePudding

Formula =LET(_x,XLOOKUP(G18,B1:P1,B2:P16),FILTER(HSTACK(A2:A16,_x),_x<>0)) works well with vertical filtering, is it possible to filter horizontally as highlighted in green color in the snapshot?

Need like this:

CodePudding user response：

You may try INDEX() with MATCH() or XMATCH() function.

=INDEX($B$2:$I$14,XMATCH($A18,$A$2:$A$14),XMATCH($C$17,$B$1:$I$1))

CodePudding user response：

Perhaps you can try using the following formula:

• Formula used in cell B20

=LET(x,FILTER(B2:P16,A20=A2:A16),
VSTACK(FILTER(B1:P1,x<>0),FILTER(x,x<>0)))