excelfilterexcel-formulaconditional-statementsdynamic-list

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?


Solution

  • You can do it with xlookup and filter like this:

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

    enter image description here