How to Use FILTER in Google Sheets

By Tomosuke Hasegawa · Updated Oct 29, 2026 · Beginner to intermediate · 7 min read · includes 2 free exercises

In short: FILTER returns the rows of a range that meet your conditions. The formula is =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, ...])

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.

ABC
1NameTeamScore
2AnnSales82
3BenSupport64
4CaraSales91
5DevSupport77
6EliSales58

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:

  1. B2:B6="Sales" checks every team cell and gives a column of TRUE/FALSE: TRUE, FALSE, TRUE, FALSE, TRUE.
  2. FILTER keeps the rows of A2:C6 where 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

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

Hint: the range is A2:C6 and the condition is B2:B6="Sales".

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.

Hint: pass a second condition as a new argument, C2:C6>60.

What to learn next

Syntax and rules in this guide were checked against Google's FILTER help page. See all guides.