# How to filter rows that match one value or another in Excel

Add the tests instead of multiplying them: `=FILTER(A2:E11,(D2:D11=H1)+(D2:D11=H2))` keeps a row when either test is TRUE.

**Excel:** `=FILTER(A2:E11,(D2:D11=H1)+(D2:D11=H2))` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G | H |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Order | Date | Customer | Region | Amount |  | Region 1 | East |
| SO-1041 | 2026-02-23 | Northwind | West | 1840 |  | Region 2 | North |
| SO-1042 | 2026-03-02 | Contoso | East | 420 |  |  |  |
| SO-1043 | 2026-03-09 | Fabrikam | West | 395 |  |  |  |
| SO-1044 | 2026-03-15 | Litware | North | 1720 |  |  |  |
| SO-1045 | 2026-03-21 | Adatum | South | 2210 |  |  |  |
| SO-1046 | 2026-03-31 | Proseware | East | 1955 |  |  |  |
| SO-1047 | 2026-04-06 | Wingtip | West | 610 |  |  |  |
| SO-1048 | 2026-04-14 | Tailspin | South | 380 |  |  |  |
| SO-1049 | 2026-04-20 | Lucerne | North | 1100 |  |  |  |
| SO-1050 | 2026-04-28 | Contoso | East | 865 |  |  |  |

Formula in G4: `=FILTER(A2:E11,(D2:D11=H1)+(D2:D11=H2))`

Result (spills 5 × 5): SO-1042, 3/2/2026, Contoso, East, $420; SO-1044, 3/15/2026, Litware, North, $1,720; SO-1046, 3/31/2026, Proseware, East, $1,955; SO-1049, 4/20/2026, Lucerne, North, $1,100; SO-1050, 4/28/2026, Contoso, East, $865

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

## Other ways

- `=FILTER(A2:E11,((D2:D11=H1)+(D2:D11=H2))>0)`: 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)`: 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=H2` adds before it compares.

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

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/filter-rows-matching-one-value-or-another