 Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

# How to FILTER by a list in Excel (including multiple lists) 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.

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.

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