The formula
Excel – AND (East and over 100)Excel
=FILTER(A2:C8,(B2:B8="East")*(C2:C8>100),"No match")In E2, spills: [Ana, East, 420], [Fay, East, 130]
Excel – OR (East or West)Excel
=FILTER(A2:A8,(B2:B8="East")+(B2:B8="West"))In I2, spills: [Ana], [Ben], [Cara], [Eli], [Fay]
Google Sheets – ANDGoogle Sheets
=FILTER(A2:C8,B2:B8="East",C2:C8>100)In E6, spills: [Ana, East, 420], [Fay, East, 130]
Google Sheets – ORGoogle Sheets
=FILTER(A2:A8,((B2:B8="East")+(B2:B8="West"))>0)In K2, spills: [Ana], [Ben], [Cara], [Eli], [Fay]
✓ Tested. All 2 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown. 2 results for formulas tagged Google Sheets were checked with a reference implementation of the documented behavior, because LibreOffice can't run those functions.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Amount | ||||||||
| 2 | Ana | East | 420 | Ana | East | 420 | Ana | Ana | |||
| 3 | Ben | West | 150 | Fay | East | 130 | Ben | Ben | |||
| 4 | Cara | East | 90 | Cara | Cara | ||||||
| 5 | Dev | North | 310 | Eli | Eli | ||||||
| 6 | Eli | West | 275 | Ana | East | 420 | Fay | Fay | |||
| 7 | Fay | East | 130 | Fay | East | 130 | |||||
| 8 | Gus | North | 60 |
How it works
- A2:C8
- The rows to return (one or many columns).
- (B2:B8="East")*(C2:C8>100)
- AND: both tests must be TRUE (1×1 = 1).
- (…)+(…)
- OR: either test TRUE gives 1 or more, which FILTER treats as TRUE.
- "No match"
- Optional if_empty value. Without it, no matches returns #CALC!.
Common errors and fixes
Why does the formula return #CALC!?
Nothing matched and there is no if_empty argument. Add "No match" as the third argument.
Why does the formula return #SPILL!?
Cells below or to the right of the formula aren't empty. Clear them so the results can spill.
Why does the formula return #VALUE!?
The include array has a different height than the data. Use the same rows (2:8) everywhere.
Does this work in Google Sheets?
Yes. Use the formulas tagged "Google Sheets" on this page; formulas tagged only "Excel" use Excel-specific syntax.