How to Use FILTER in Google Sheets: Comprehensive Guide

FILTER returns only the rows (or columns) of a range that meet the conditions you set, and updates automatically when the data changes. It replaces manual filter views, helper columns and most VLOOKUP workarounds for "get me all the rows where...".

Syntax

=FILTER(range, condition1, [condition2, ...])

range: The data to filter, which can span several columns.
condition1: A column (or row) of TRUE/FALSE values, or an expression that produces one, with the same number of rows as range. Only rows where the condition is TRUE are returned.
condition2, ... [Optional]: More conditions. A row must satisfy all of them (AND logic).

The result spills into as many cells as it needs below and to the right of the formula, so those cells must be empty.

Basic examples

With data in A1:C100 (Name, Department, Salary) and headers in row 1:

=FILTER(A2:C100, B2:B100 = "Sales")

Returns every row where the department is Sales.

=FILTER(A2:C100, C2:C100 > 50000)

Returns every row with a salary above 50,000.

Return only some columns by choosing a narrower range. This returns just the names of the Sales team:

=FILTER(A2:A100, B2:B100 = "Sales")

Several conditions

AND: all conditions must be true

Pass more than one condition:

=FILTER(A2:C100, B2:B100 = "Sales", C2:C100 > 50000)

OR: at least one must be true

Add the conditions together. TRUE counts as 1, so a sum above 0 means at least one is true:

=FILTER(A2:C100, (B2:B100 = "Sales") + (B2:B100 = "IT"))

Mixing AND and OR

=FILTER(A2:C100, ((B2:B100 = "Sales") + (B2:B100 = "IT")) * (C2:C100 > 50000))

Multiplication is AND, addition is OR. Use parentheses to be explicit.

Useful conditions

Not blank: A2:A100 <> ""
Contains text: ISNUMBER(SEARCH("urgent", A2:A100))
Regular expression: REGEXMATCH(A2:A100, "^INV-\d+")
Date range: (D2:D100 >= DATE(2026,1,1)) * (D2:D100 <= DATE(2026,3,31))
Value from a cell: B2:B100 = G1, where G1 could be a dropdown, which makes the filter interactive
In a list: ISNUMBER(MATCH(B2:B100, J2:J10, 0))
Top-level not equal: B2:B100 <> "Sales"

Handling "no matches"

When nothing matches, FILTER returns #N/A ("No matches are found in FILTER evaluation"). Show something friendlier with IFERROR:

=IFERROR(FILTER(A2:C100, B2:B100 = G1), "No results")

Sorting and cleaning the result

FILTER keeps the original order. Wrap it in SORT to order the output:

=SORT(FILTER(A2:C100, B2:B100 = "Sales"), 3, FALSE)

This sorts the filtered rows by the third column, descending. Wrap in UNIQUE to remove duplicates, and in ARRAYFORMULA or TRANSPOSE when you need to reshape the result.

Filtering columns instead of rows

To filter columns, use a condition that is a row with the same width as the range. Hiding the columns whose header is Notes:

=FILTER(A1:F100, A1:F1 <> "Notes")

Filter across sheets and files

Point the range at another sheet: =FILTER(Data!A2:C, Data!B2:B = "Sales"). For another file, wrap the range in IMPORTRANGE:

=FILTER(IMPORTRANGE("url", "Data!A2:C100"), IMPORTRANGE("url", "Data!B2:B100") = "Sales")

For anything this heavy, a single QUERY is often cleaner.

Common errors

#N/A: No matches. Nothing satisfied the conditions. Check for spaces and text-versus-number mismatches, or wrap in IFERROR.
#REF!: Array result was not expanded because it would overwrite data. Something is in the way of the spill area. Clear the cells below and to the right.
#VALUE!: Mismatched array sizes. The condition does not have the same number of rows as range. FILTER(A2:C100, B2:B50 = ...) fails; both ranges must match.
Only part of the data shows. Open-ended ranges like A2:C and B2:B are fine, but mixing A2:C with B2:B100 is a size mismatch.

FILTER vs. VLOOKUP vs. QUERY

VLOOKUP and XLOOKUP return one match. FILTER returns all matches. QUERY can do everything FILTER does plus sorting, grouping and aggregation, at the cost of a text-based syntax. FILTER is the easier choice when you just need the matching rows.

Frequently asked questions

Does FILTER change my source data? No. It only displays a filtered copy, and the original stays intact.

Can I filter on the result of another formula? Yes. A condition can be any expression that returns an array of true/false values.

Can I edit the filtered cells? No, the spilled results are formula output. Edit the source data instead.

Related Functions

QUERY: A SQL-like way to filter, sort and aggregate in one formula.
SORT: Sorts the rows of a range.
UNIQUE: Returns the distinct rows of a range.
XLOOKUP: Returns the first match instead of all matches.
ARRAYFORMULA: Applies a formula to a whole range.

FILTER