# FILTER function in Google Sheets

> FILTER returns the rows of a range that meet the conditions you give it.

Source: https://spreadsheetpractice.com/google-sheets/functions/filter · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** 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)
- **Works in:** Google Sheets, with different arguments from Excel
- **Returns:** An array of the matching rows. #N/A when no row matches.

## What it does

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.

## 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 was calculated by running the formula on the sample sheet in Google Sheets mode.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | `=FILTER(A2:C9,B2:B9="East",C2:C9>20)` | Ana (spills 2 × 3) | Two conditions, two arguments: East and more than 20 units |
| I2 | `=FILTER(A2:A9,(B2:B9="North")+(B2:B9="West"))` | Raj (spills 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 |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F | G | H | I | J | K |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Units |  | East, over 20 |  |  |  | North or West |  | South |
| 2 | Ana | East | 40 |  | Ana | East | 40 |  | Raj |  | No matches |
| 3 | Raj | West | 25 |  | Mo | East | 30 |  | Kim |  |  |
| 4 | Lee | East | 12 |  |  |  |  |  | Sam |  |  |
| 5 | Kim | North | 55 |  |  |  |  |  | Eve |  |  |
| 6 | Mo | East | 30 |  |  |  |  |  |  |  |  |
| 7 | Sam | West | 18 |  |  |  |  |  |  |  |  |
| 8 | Eve | North | 22 |  |  |  |  |  |  |  |  |
| 9 | Ivy | East | 9 |  |  |  |  |  |  |  |  |

## 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.

See [the Excel FILTER function](https://spreadsheetpractice.com/functions/filter).

## 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.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
