How to filter by a list of values in Excel
Formula (Excel and Google Sheets)
=FILTER(A2:E11,COUNTIF(G2:G4,C2:C11))Use COUNTIF as the condition: =FILTER(A2:E11,COUNTIF(G2:G4,C2:C11)) keeps every row whose customer appears in the list in G2:G4.
- Result on the example
- a 4-row list that spills from I2 in
I2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in I2. Change any input and the result updates, or edit the formula to experiment.
How it works
With a whole column as its second argument, COUNTIF runs once per order: for each customer in C2:C11 it counts how many times that name appears in the list. The result is 1 for listed customers and 0 for the rest, and FILTER keeps the rows that aren't 0.
Edit the list and the result follows, so the list works like a set of checkboxes for the report.
=FILTER(A2:E11,COUNTIF(G2:G4,C2:C11))Result{4 rows × 5 cols} "SO-1042", 46083, "Contoso", "East", …
A2:E1110 rows × 5 cols: "SO-1041", 2/23/2026, "Northwind", "West", …A range: 10 rows × 5 columns.
G2:G43 rows × 1 col: "Contoso", "Litware", "Wingtip"A range: 3 cells in one column.
C2:C1110 rows × 1 col: "Northwind", "Contoso", "Fabrikam", "Litware", …A range: 10 cells in one column.
COUNTIF(G2:G4,C2:C11){10 rows × 1 col} 0, 1, 0, 1, …COUNTIF: Counts cells that meet one condition.- range
G2:G43 rows × 1 col: "Contoso", "Litware", "Wingtip" - criteria
C2:C1110 rows × 1 col: "Northwind", "Contoso", "Fabrikam", "Litware", …
- range
FILTER(A2:E11,COUNTIF(G2:G4,C2:C11)){4 rows × 5 cols} "SO-1042", 46083, "Contoso", "East", …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:E1110 rows × 5 cols: "SO-1041", 2/23/2026, "Northwind", "West", … - condition1
COUNTIF(G2:G4,C2:C11){10 rows × 1 col} 0, 1, 0, 1, …
- range
In Google Sheets
The same formula works in Google Sheets: =FILTER(A2:E11,COUNTIF(G2:G4,C2:C11)). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 4 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =FILTER(A2:E11,ISNUMBER(MATCH(C2:C11,G2:G4,0))) | spills 4 × 5 | MATCH returns a position for listed customers and #N/A for the rest. Use it if the names can contain * or ?, which COUNTIF reads as wildcards. |
| =FILTER(A2:E11,COUNTIF(G2:G4,C2:C11)=0) | spills 6 × 5Different result | Add =0 to do the opposite and leave the listed customers out. Every order except the ones for listed customers. |
Common mistakes
- The ranges go in this order: the list first, then the column to check.
COUNTIF(C2:C11,G2:G4)returns three counts, not one per order, and FILTER fails. - A trailing space in the list ("Contoso ") stops it matching. TRIM the list if it was pasted from somewhere else.
- Make the list range taller than you need, such as
G2:G20. Blank cells in it do no harm, and new names are picked up.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Filter by a list of values
I2, return every order whose customer is one of the names listed in G2:G4.Questions
How do I exclude a list of values instead?
Test for a count of 0: =FILTER(A2:E11,COUNTIF(G2:G4,C2:C11)=0).
Does this work in Google Sheets?
Yes, the same formula works. FILTER evaluates the COUNTIF once per row without ARRAYFORMULA.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.