CODEWITHSUNDEEP
excelfilterexcel-formulaconditional-statementsdynamic-list
reminiscience
reminiscience

Reputation: 3

Filter columns based on a matched row

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: Excel example

So, I could manage to output the row with the 0s and 1s, but I actually want the courses (Row 3) to be output. And how can I filter them based on a condition (if cell value in the found row is 0, then output the column name in row 3)?

Why do I need this? Some clients want to know which courses need to be completed, so I want to have a dynamic list which I can output just by choosing the client’s ID in column A.

Any idea how to approach this?

Upvotes: 0

Views: 765

Answers (2)

reminiscience
reminiscience

Reputation: 3

Try this:

=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))

Upvotes: 0

Tom Sharpe
Tom Sharpe

Reputation: 34180

You can do it with xlookup and filter like this:

=FILTER(B3:E3,XLOOKUP(H3,A4:A7,B4:E7)=0)

enter image description here

Upvotes: 1

Related Questions