I basically want to achieve the following:

1. Find the row based on a cell value
2. Output the column names, however
3. Filter the column names based on the cell values in the row

So I have a list of clients in column A (listed in rows) and a list of e-learning courses on 15 adjacent columns (listed in a single row). I have a dropdown list with the clients. I want to output a list of e-learning courses after I choose a single client, and the list has to be filtered based on the cell value (1 for „This course is completed“, 0 for „This course has not been completed“). I want to output the non-completed courses only.

The matrix looks as follows:

CodePudding user response：

I think I found a solution:

=TRANSPOSE(FILTER($B$3:$Q$3;(INDIRECT(ADDRESS(XMATCH($T$5;$A$4:$A$84) 3;2)&":"&ADDRESS(XMATCH($T$5;$A$4:$A$84) 3;17)))=0))

Maybe not that elegant.