FILTER function in Google Sheets
Quick answer
FILTER returns the rows of a range that meet the conditions you give it.
- Syntax
=FILTER(range, condition1, [condition2, ...])- Most common use
=FILTER(A2:C9,B2:B9="East",C2:C9>20)returns Ana (Two conditions, two arguments: East and more than 20 units)
In Google Sheets each condition is its own argument, and a row comes back only when every condition is TRUE. The results fill the cells below and update when the data changes.
- Works in
- Google Sheets, with different arguments from Excel
- Returns
- An array of the matching rows. #N/A when no row matches.
Syntax
=FILTER(range, condition1, [condition2, ...])
| Argument | Required? | What it is |
|---|---|---|
range | Required | The rows to return, such as A2:C9. It can be one column or several. |
condition1 | Required | A column of TRUE/FALSE tests the same height as range, such as B2:B9="East". Add more conditions as extra arguments; a row is kept only when all of them are TRUE. |
Examples
Each result is calculated from the sample sheet by our formula engine in Sheets mode, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | =FILTER(A2:C9,B2:B9="East",C2:C9>20) | Anaspills 2 × 3 | Two conditions, two arguments: East and more than 20 units |
| I2 | =FILTER(A2:A9,(B2:B9="North")+(B2:B9="West")) | Rajspills 4 × 1 | OR: add the tests inside one condition, so a row passes when either is TRUE |
| K2 | =IFNA(FILTER(A2:A9,B2:B9="South"),"No matches") | No matches | There are no South rows, so FILTER returns #N/A and IFNA shows a message instead |
Try it live
This is the sample sheet. Change any value or edit a formula, and the results update.
Practice FILTER
Graded instantly in Sheets mode, then re-checked on changed data.
Big orders from one region
E2, list every order (columns A to C) from the West region with an amount over 500. Give FILTER each condition as its own argument.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #N/A with "No matches are found in FILTER evaluation" | No row passed every condition. Check the spelling in text tests, or show a fallback: =IFNA(FILTER(A2:A9,B2:B9="South"),"None"). |
| An error saying FILTER has mismatched range sizes | Every condition must cover the same rows as range. A2:A9 with B2:B10 fails; start and end all the ranges on the same rows. |
| #REF! with "Array result was not expanded" | Something is typed in the cells the results need. FILTER needs empty cells below it, and to the right when it returns several columns. |
Tips
- Sort the results by wrapping them: =SORT(FILTER(A2:C9,B2:B9="East"),3,FALSE).
- Point a condition at a cell, such as B2:B9=F1, so anyone can change the filter without editing the formula.
- Match part of the text with REGEXMATCH as the condition: =FILTER(A2:A9,REGEXMATCH(B2:B9,"East|West")).
FILTER in Excel
Excel's FILTER takes one include argument plus an optional if_empty value, so several conditions go inside it, joined with * for AND and + for OR, and it shows #CALC! when nothing matches. Sheets takes each condition as its own argument and has no if_empty, so you wrap it in IFNA.
Questions
How do I use FILTER with multiple conditions in Google Sheets?
Give each condition its own argument: =FILTER(A2:C9,B2:B9="East",C2:C9>20). A row comes back only when all of them are TRUE. For OR, add the tests inside one argument: (B2:B9="East")+(B2:B9="West").
What does FILTER return when nothing matches?
#N/A, with the message "No matches are found in FILTER evaluation." Wrap it in IFNA to show text or a blank instead.
Does an Excel FILTER formula work in Google Sheets?
Often, as long as it uses only range and include, since * and + between tests work in both. An Excel if_empty third argument is read as another condition in Sheets, so replace it with IFNA.
Updated Checked by running: every formula on this page was run by our formula engine on .