Forum Discussion
Filter multiple columns
I am working on a list of reporters and the topics they cover. Since most reporters cover multiple topics, I have three columns for primary, secondary, and tertiary topics of interest. I'm trying to create a user-friendly filter that would allow me or another user to filter all reporters who cover a specific topic area. For example, if I wanted to find someone who covered climate change as a primary, secondary, or tertiary topic of interest, I want to have a filter that would show me reporters who cover climate change as a primary, secondary, or tertiary topic of interest instead of only reporters who cover it as a primary topic of interest. I would ideally also like to still be able to filter by location, but if that isn't possible it's fine. The priority is the topic areas. Also open to any suggestions on design change.
I've attached a solution that uses FILTER, a new function. This will only work if you have the latest version of Excel
The function below is in cell B13 of the attached. In cell E10 you can select (from a drop down list) any of the several categories and see the reporters who cover the topic.
=FILTER(B3:E8,(C3:C8=E10)+(D3:D8=E10)+(E3:E8=E10))
4 Replies
I am not sure if I got it.
But isn't it possible with a simple table?
Simply filter through to the result.
Enclosed your file with table.
If this information helped you, please mark this post as "Correct answer" and click on like (hand with thumbs up), so that other members can benefit from this information later.
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
I've attached a solution that uses FILTER, a new function. This will only work if you have the latest version of Excel
The function below is in cell B13 of the attached. In cell E10 you can select (from a drop down list) any of the several categories and see the reporters who cover the topic.
=FILTER(B3:E8,(C3:C8=E10)+(D3:D8=E10)+(E3:E8=E10))
- This was really helpful thank you so much!
- A point of clarification: the FILTER function is new...what that formula does is filter the range desired (B3:E8 in the example) based on meeting either a single criterion or a combination of criteria. In this case the criteria are several (three) and they're A OR B OR C, which is represented by the "+" sign between them. To make it a case of meeting all three criteria, you'd intersperse (*) between the parentheses. Here's a video that explains FILTER further. https://www.youtube.com/watch?v=9I9DtFOVPIg
