Lookup
FILTER
Definition
FILTER returns the rows or columns of a range that pass a test.
Syntax
=FILTER(array, include)
Arguments
| Argument | Meaning |
|---|---|
array |
The cells to return from, such as B2:B4. |
include |
The test, the same height or width as array, such as A2:A4="pine". |
Worked example
A lumber yard lists wood types in A2:A4 and plank counts in B2:B4.
| A: Type | B: Planks |
|---|---|
| oak | 4 |
| pine | 9 |
| oak | 2 |
Formula: =INDEX(FILTER(B2:B4,A2:A4="pine"),1)
Result: 9. The pine row holds 9, and INDEX takes that first match so the cell stays a single value.
Practice
Cells A2:A4 hold a, b, and a. Cells B2:B4 hold 10, 20, and 30. In C2, write a formula that returns the first value from B2:B4 where A2:A4 equals b.
- In C2, write an INDEX and FILTER formula that returns the first value in B2:B4 where A2:A4 equals b.
- C2 should show the first value in B2:B4 where A2:A4 equals b.
Step 1 of 2
Enter =INDEX(FILTER(B2:B4,A2:A4="b"),1) in C2.
b appears in A3, and B3 is 20.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
C2 uses FILTER.
C2 now shows 20.
Related functions
INDEX can pick one item from the filtered list. MATCH can find a position if you need that instead of the value.
Common mistake
Leaving FILTER to spill into C2:C3 when the checker reads only C2: wrap INDEX around FILTER so C2 holds one number.