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.

**Table of Contents**

**Download the example file: **Join the free Insiders Program and gain access to the example file used for this post.

**File name:** 0146 Filter by a list.xlsx

**Watch the video**

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

Discover how you can automate your work with our Excel courses and tools.

**Excel Academy**

The complete program for saving time by automating Excel.

**Excel Automation Secrets**

Discover the 7-step framework for automating Excel.

**Office Scripts: Automate Excel Everywhere**

Start using Office Scripts and Power Automate to automate Excel in new ways.