How to Filter by a List of Values in Excel

To filter by a list in Excel, you need rows whose value matches any item on a separate list: these five product codes, those three regions, the customers on a watch list. Typing one OR condition per item gets long fast. Instead, give FILTER a COUNTIF test that checks every row against the whole list at once. This guide shows that formula, how to exclude a list, and options for older Excel.

All the examples use this sales table in A1:C9, with the regions to keep listed in E2:E3:

How to filter by a list with FILTER and COUNTIF#

Use COUNTIF as FILTER's test, with the list as the range and the data column as the criteria:

=FILTER(A2:C9,COUNTIF(E2:E3,B2:B9))

The result spills four rows: Acme, Cora, Elan and Gala, the North and East customers. Here is how it works:

1. COUNTIF(E2:E3,B2:B9) asks, for each region in B2:B9, "how many times is this on the Keep list?" It returns one number per row: 1, 0, 1, 0, 1, 0, 1, 0.
2. FILTER treats any non-zero number as TRUE and 0 as FALSE.
3. FILTER returns the rows of A2:C9 where the test is TRUE.

Notice the order: the list goes first in COUNTIF, and the data column goes second. Swap them and you get the wrong counts. FILTER needs Excel 2021 or Microsoft 365. The FILTER function guide and the COUNTIF function guide cover each function on its own, and the article on the FILTER function with AND and OR explains the basics.

The ISNUMBER and MATCH alternative#

ISNUMBER with MATCH does the same job and some people find it easier to read:

=FILTER(A2:C9,ISNUMBER(MATCH(B2:B9,E2:E3,0)))

MATCH looks for each region in the list and returns its position, such as 1 or 2, or #N/A when it isn't there. ISNUMBER turns positions into TRUE and #N/A into FALSE. The result is the same four rows. Here the data column comes first, because MATCH asks "where is this value in the list?". In Microsoft 365, XMATCH works too: ISNUMBER(XMATCH(B2:B9,E2:E3)). The ISNUMBER function guide explains why it is so useful for turning errors into FALSE.

Use whichever you prefer. COUNTIF is a little shorter; MATCH reads more like plain English.

How to exclude a list of values#

To remove the rows on the list and keep everything else, test for a count of zero:

=FILTER(A2:C9,COUNTIF(E2:E3,B2:B9)=0)

This returns Bolt, Dyna, Fino and Hart: the South and West customers. The MATCH version is =FILTER(A2:C9,NOT(ISNUMBER(MATCH(B2:B9,E2:E3,0)))). Exclusion lists are handy for removing test accounts, internal emails or cancelled orders.

Combine the list with other conditions#

Multiply the list test by another condition to require both. To keep North and East customers with amounts of 300 or more:

=FILTER(A2:C9,(COUNTIF(E2:E3,B2:B9)>0)*(C2:C9>=300))

The result is Acme (500) and Gala (350). The >0 turns the counts into TRUE or FALSE before multiplying, which keeps the test clean even if a value appears twice on the list. The guide to FILTER with multiple criteria explains how * means AND and + means OR. For partial text matches instead of a list, see how to filter rows that contain text.

To total the matching rows instead of listing them, wrap the result: =SUM(FILTER(C2:C9,COUNTIF(E2:E3,B2:B9))) returns 1,250.

Make the list grow automatically#

Turn the list into an Excel table so the formula picks up new values. Select E1:E3, press Ctrl+T (Mac: ⌘T) and name the table Keep. Then write:

=FILTER(A2:C9,COUNTIF(Keep[Keep],B2:B9))

Type "South" under the table and the filter result updates at once. Add a third argument to show a message when nothing matches: =FILTER(A2:C9,COUNTIF(E2:E3,B2:B9),"No matches"). Without it, an empty result shows #CALC!.

Filter by a list in older Excel#

Without FILTER, you have two good options:

Advanced Filter#

1. Give the list the same header as the data column: change E1 from "Keep" to Region.
2. Click inside the data and choose Data > Advanced (in the Sort & Filter group).
3. Set the List range to A1:C9 and the Criteria range to E1:E3.
4. Choose Copy to another location if you want the result elsewhere, then click OK.

Advanced Filter treats each row of the criteria range as an OR condition. It doesn't update by itself, so run it again when the data changes.

A COUNTIF helper column#

Add a column D with =COUNTIF($E$2:$E$3,B2)>0 and fill it down. Then turn on the filter buttons (Windows: Ctrl+Shift+L; Mac: ⌘⇧F) and filter column D for TRUE. This works in every version of Excel and is easy to check.

Why filter by list returns the wrong rows#

When rows are missing, the values don't match exactly. Check these:

1. Extra spaces. "North " with a trailing space doesn't match "North". Clean both sides with TRIM.
2. Numbers stored as text. Product code 1050 as a number doesn't match "1050" as text. Make both the same type.
3. Arguments swapped. In COUNTIF the list comes first; in MATCH the data column comes first.
4. Ranges of different heights. The test range (B2:B9) must have as many rows as the data (A2:C9), or FILTER shows #VALUE!.

Matching is not case-sensitive, so "north" matches "North". To show the matches as one comma-separated cell instead of rows, see how to list matches in one cell.

Practise it#

Filtering by a list combines two skills: FILTER for returning rows, and COUNTIF for testing each row. Practise the FILTER function in a live spreadsheet, then practise COUNTIF to get comfortable with ranges and criteria. Each lesson checks your formula instantly. Then try the exercise on this page to filter the sales table by the Keep list.