Forum Discussion

katameringue's avatar
katameringue
Copper Contributor
Nov 13, 2023

Filtering against numerous categories

I am trying to build out a table where numerous individuals need to be captured, with a variety of categories against their names, that I want to be able to search. I'm struggling to come up with the cleanest way to filter the data. 

 

I've included two ways I've formatted this so far as an indication of my problem. Table 1 means I have to duplicate a person's details to capture the full range of their expertise. Table 2 is impractical for filtering, where each column filter would have to simply be Y / (blank).

 

My ultimate goal would be, for example, to be able to filter to find a person who is a specialist in Tech Artifacts in Retail (e.g. Person 1 & 3), or anybody with Risk knowledge (e.g. Persons 1 and 2). 

 

Any suggestions? Thank you

 

TABLE 1

 

TABLE 2

 

excel
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Nov 13, 2023

    katameringue 

    =UNIQUE(FILTER(A3:A8,((E3:E8=I1)*(F3:F8=J1))+(G3:G8=K1)))

     

    With this multiple filter criteria we can return a person who is a specialist in Tech Artifacts in Retail or anybody with Risk knowledge at the same time. With UNIQUE each person is only returned once because Person 1 is specialist in both categories and would be returned twice without UNIQUE.

Resources