Lookup
Keeping rows that pass a test
Definition
Returning the numbers whose labels match one group means giving FILTER the numbers and the match test.
Syntax
=FILTER(array, include)
Arguments
| Argument | Meaning |
|---|---|
array |
The column of numbers to return. |
include |
The test for which rows to keep, for example A2:A4="on". |
Worked example
A pump log stores status in A2:A4 and litres in B2:B4. Only the on row should return.
| A: Status | B: Litres |
|---|---|
| off | 4 |
| on | 13 |
| off | 6 |
Formula: =FILTER(B2:B4,A2:A4="on")
Result: 13. Only row 3 is on, so the litres 13 are returned.
Practice
Cells A2:A4 hold no, yes, and no. Cells B2:B4 hold 5, 8, and 2. In C2, write a formula that returns the numbers in B2:B4 where A2:A4 equals yes.
- In C2, write an INDEX and FILTER formula that returns the first value in B2:B4 where A2:A4 equals yes.
- C2 should show the first value in B2:B4 where A2:A4 equals yes.
Step 1 of 2
Enter =INDEX(FILTER(B2:B4,A2:A4="yes"),1) in C2.
yes appears in A3, and B3 is 8.
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 8.
The next lesson is Dropping repeats from a list.
Related functions
FILTER is the function that keeps the numbers whose labels pass the test.
Common mistake
Swapping the columns, as in =FILTER(A2:A4,B2:B4="on"): the formula then tries to match litres to the word on and returns nothing.