FILTER function in Google Sheets
FILTER returns the rows of a range that meet the conditions you give it. 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.
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine in Sheets mode.