A few weeks back, I wrote a post about how to filter by a list in Power Query. It didn’t take long for somebody to ask if the same is possible in Excel. The answer is, YES! So, that’s what we are looking at in this post: how to use Excel’s FILTER function based on a list.
Note: The solution in this post only works in Excel 2021 and Excel 365.
Download the example file: Click the link below to download the example file used for this post:
Watch the video
Contents
Understanding the FILTER function
Let’s start by looking at the FILTER function. FILTER has a simple syntax with just three arguments:
=FILTER(array, include, [if_empty])- array: The range of cells, or array of values to filter.
- include: An array of TRUE/FALSE results, where only the TRUE values are retained in the filter.
- [if_empty]: The value to display if no rows are returned.
The include argument is where we need to focus our attention for the current requirements.
For each row of the source data, there needs to be a calculation that returns TRUE or FALSE. Only the TRUE values are retained in the FILTER result.
Therefore, the critical question is: How can we create a TRUE/FALSE result for each item? Let’s find out.
Want to know more about the FILTER function? Check out this post: FILTER function in Excel (How to + 8 Examples)
Calculating the TRUE/FALSE value
To calculate TRUE or FALSE, we could use the COUNTIFS function:
The formula in cell I4 is:
=COUNTIFS(ItemList[Item],Data[Item])This is taking the values in the ItemList table and counting how many times they appear for each row of the Data table. This function uses dynamic arrays and spills the results.
You might be thinking that they are not TRUE or FALSE values but are 0’s and 1’s.
But the great thing about Excel is that 0 is deemed to be FALSE, and any other values are treated as TRUE. So, for Excel, this is the same as a TRUE/FALSE list. Which means we can use this inside the include argument.
Want to know more about COUNTIFS? Check out this post: https://exceljet.net/functions/countifs-function
FILTER based on a list
OK, now it’s time to add the FILTER function, using the COUNTIFS as the include argument.
The formula in cell I4 is:
=FILTER(Data,COUNTIFS(ItemList[Item],Data[Item]),"No values")The previous COUNTIFS formula is highlighted in bold.
Only the items from the Data table where the COUNTIFS calculates to 1 or more are retained.
FILTER by items NOT in the list
To filter by items not in the list, we wrap the COUNTIFS in the NOT function.
=FILTER(Data,NOT(COUNTIFS(ItemList[Item],Data[Item])),”No values”)
FILTER based on multiple lists
I know somebody will ask this, so I might as well answer it here.
Yes, using this method we can filter by multiple lists. We just add more COUNTIFS into the include argument.
And condition (included in all lists)
If we want items that appear in multiple lists:
- Use an asterisk ( * ) between each COUNTIFS
- Wrap all the COUNTIFS in the include argument in brackets
The formula in Cell I4 is:
=FILTER(Data,(COUNTIFS(ItemList[Item],Data[Item])*COUNTIFS(RegionList[Region],Data[Region])),"No values")Notice the * symbol between the COUNTIFS. The items included in both the ItemList and RegionList are retained by the FILTER result.
Or condition (included in any lists)
Alternatively, to retain items that appear in any list:
- Use a plus ( + ) between each COUNTIFS
- Wrap all the COUNTIFS in the include argument in brackets,
The formula in Cell I4 is:
=FILTER(Data,(COUNTIFS(ItemList[Item],Data[Item])+COUNTIFS(RegionList[Region],Data[Region])),"No values")Notice the + symbol between the COUNTIFS. Items included in either the ItemList or RegionList are retained by the FILTER result.
Conclusion
In this post, we’ve seen how to break down the formula logic of the FILTER function. Through this, we were able to filter by a list in Excel using FILTER and COUNTIFS. We also took this further for and / or logic to filter by multiple lists.
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.
But, if you're still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:
