How to Use FILTER in Google Sheets
=FILTER(range, condition1, [condition2, ...]), where each condition is a column of TRUE/FALSE values the same length as the range.
What FILTER does
VLOOKUP and XLOOKUP return one value. FILTER returns every row that matches. Give it a table and a test, and it hands back only the rows that pass the test, as a live list that updates when the data changes.
FILTER syntax
=FILTER(range, condition1, [condition2, ...])
range: the data to filter, such asA2:C6.condition1: a column of TRUE/FALSE values that lines up with the rows ofrange. Most often this is a test such asB2:B6="Sales".condition2, ...(optional): more TRUE/FALSE columns.
Each condition must be exactly as long as the range. Conditions must all be rows or all be columns, not a mix.
A step-by-step example
Here is a small staff table in cells A1:C6.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Team | Score |
| 2 | Ann | Sales | 82 |
| 3 | Ben | Support | 64 |
| 4 | Cara | Sales | 91 |
| 5 | Dev | Support | 77 |
| 6 | Eli | Sales | 58 |
To list only the people on the Sales team:
=FILTER(A2:C6, B2:B6="Sales")
This returns three rows: Ann, Cara and Eli, each with their team and score. Here is why:
B2:B6="Sales"checks every team cell and gives a column of TRUE/FALSE: TRUE, FALSE, TRUE, FALSE, TRUE.- FILTER keeps the rows of
A2:C6where that column is TRUE.
The result fills the cells below and to the right of the formula. Those cells must be empty, or Sheets shows an error saying the array result was not expanded because it would overwrite data.
Use more than one condition
Add another condition as a new argument. A row is kept only when all conditions are TRUE. To list Sales people with a score above 60:
=FILTER(A2:C6, B2:B6="Sales", C2:C6>60)
This returns Ann and Cara.
To keep a row when either condition is TRUE, add the tests together inside one condition. This lists people on the Support team or with a score above 90:
=FILTER(A2:C6, (B2:B6="Support")+(C2:C6>90))
This returns Ben, Cara and Dev.
Handle "no matches"
If no row meets the conditions, FILTER returns #N/A. Wrap it in IFERROR to show a message instead:
=IFERROR(FILTER(A2:C6, C2:C6>100), "No results")
Common FILTER mistakes
- Conditions of the wrong size. If the range is
A2:C6(5 rows) and the condition isB2:B7(6 rows), FILTER returns an error. Keep them the same length. - Output cells that are not empty. Clear the cells below and to the right of the formula.
- Filtering rows and columns together. FILTER works on one direction at a time. To filter both, use one FILTER as the range of another.
- Forgetting quotation marks around text, as in
B2:B6="Sales".
Try it yourself
Use the staff table above (A1:C6). Type your formula into the box, starting with =, then press Check.
Exercise 1: Filter one team
Write a formula that returns all rows (columns A to C) for people on the Sales team (column B).
Exercise 2: Two conditions
Write a formula that returns the rows for people on the Sales team whose score (column C) is greater than 60.
What to learn next
- XLOOKUP: look up a value in any direction.
- SUMIF: add up the rows that match a condition.
- QUERY: filter, group and sort with one formula.
- SORT: sort a range with a formula.
- UNIQUE and SORT: remove duplicates and sort a list, often together with FILTER.
Syntax and rules in this guide were checked against Google's FILTER help page. See all guides.