How to filter rows that match one value or another in Excel
Formula (Excel and Google Sheets)
=FILTER(A2:E11,(D2:D11=H1)+(D2:D11=H2))Add the tests instead of multiplying them: =FILTER(A2:E11,(D2:D11=H1)+(D2:D11=H2)) keeps a row when either test is TRUE.
- Result on the example
- a 5-row list that spills from G4 in
G4 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in G4. Change any input and the result updates, or edit the formula to experiment.
How it works
In a calculation TRUE counts as 1 and FALSE as 0. Adding the two tests gives 1 for a row that matches either region and 0 for a row that matches neither, and FILTER keeps every row whose value isn't 0. That's OR logic; multiplying would be AND.
The rows come back in their original order, East and North mixed together.
=FILTER(A2:E11,(D2:D11=H1)+(D2:D11=H2))Result{5 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.
D2:D1110 rows × 1 col: "West", "East", "West", "North", …A range: 10 cells in one column.
H1"East"The value in H1.
D2:D11=H1{10 rows × 1 col} FALSE, TRUE, FALSE, FALSE, …The = operator tests whether the two sides are equal.
H2"North"The value in H2.
D2:D11=H2{10 rows × 1 col} FALSE, FALSE, FALSE, TRUE, …The = operator tests whether the two sides are equal.
D2:D11=H1)+(D2:D11=H2{10 rows × 1 col} 0, 1, 0, 1, …The + operator adds.
FILTER(A2:E11,(D2:D11=H1)+(D2:D11=H2)){5 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
D2:D11=H1)+(D2:D11=H2{10 rows × 1 col} 0, 1, 0, 1, …
- range
In Google Sheets
The same formula works in Google Sheets: =FILTER(A2:E11,(D2:D11=H1)+(D2:D11=H2)). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 5 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =FILTER(A2:E11,((D2:D11=H1)+(D2:D11=H2))>0) | spills 5 × 5 | The >0 turns the sum back into TRUE and FALSE. Same result, and safer when the two tests can both be TRUE on one row and you reuse the sum elsewhere. |
| =SORT(FILTER(A2:E11,(D2:D11=H1)+(D2:D11=H2)),4) | spills 5 × 5Different result | Wrap it in SORT to group the regions together. Sorted by region: the East orders first, then North. |
Common mistakes
OR(D2:D11=H1,D2:D11=H2)returns a single TRUE for the whole range, so FILTER fails with #VALUE!. Use + between bracketed tests.- Leave out the brackets and the formula compares the wrong things:
D2:D11=H1+D2:D11=H2adds before it compares.
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 rows that match one value or another
G4, return every order from either the region in H1 or the region in H2.Questions
How do I combine OR with AND?
Bracket the OR part and multiply it by the AND test: =FILTER(A2:E11,((D2:D11=H1)+(D2:D11=H2))*(E2:E11>=1000)).
What if I have more than two values?
Put them in a range and test against the whole list: =FILTER(A2:E11,COUNTIF(H1:H5,D2:D11)).
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.