FILTER Function in Google Sheets: Complete Guide

The FILTER function in Google Sheets returns rows from a range that match one or more conditions. It creates a dynamic filtered view without modifying your source data, the results update automatically whenever the original data changes.

This guide covers the FILTER syntax, step-by-step examples with single and multiple conditions, AND vs OR logic, combining FILTER with SORT, common errors, and a comparison with QUERY.

FILTER Syntax

Here is the full FILTER syntax in Google Sheets:

FILTER returns every row where all conditions evaluate to TRUE. The results spill into adjacent cells below the formula.

How to Use FILTER: Step-by-Step

Here is a product inventory table with 8 products. The goal is to filter products in the “Surveillance” category.

Select cell H2. Enter this formula:

• A2:F9 is the range, all data rows across all columns
• C2:C9="Surveillance" is the condition, return rows where Category equals “Surveillance”

Press Enter. FILTER returns two rows: Field Binoculars (SKU-104) and Evidence Bag Kit (SKU-107). Both have “Surveillance” in the Category column.

The results spill into cells H2:M3 automatically. FILTER always returns the full rows (or whichever columns you included in the range argument).

To filter Surveillance products priced above $20, add a second condition:

Each condition separated by a comma acts as AND logic, both must be TRUE for a row to appear.

Only Field Binoculars ($65.00) matches both conditions. The Evidence Bag Kit ($15.00) is excluded because its price is not above $20.

FILTER with OR Conditions

Comma-separated conditions are AND logic. For OR logic, use the + operator and wrap the combined condition in parentheses.

This formula filters products in the “Surveillance” OR “Optics” category:

The + operator combines the two conditions with OR logic. A row appears if either condition is TRUE.

FILTER returns four rows: Magnifying Glass and Brass Telescope (Optics), plus Field Binoculars and Evidence Bag Kit (Surveillance).

You can combine AND and OR in the same formula. Use + for OR within parentheses, and commas for AND between conditions:

This returns Surveillance or Optics products priced above $20. The Evidence Bag Kit ($15.00) is excluded.

FILTER Examples

Example 1: Filter by Price Range

To find products priced between $20 and $50:

Both price conditions use commas (AND logic). The result includes every product where the price is at least $20 and at most $50.

Example 2: Filter with Partial Text Match

FILTER conditions are exact comparisons by default. To filter rows containing specific text, combine FILTER with SEARCH and ISNUMBER:

SEARCH("Baker", F2:F9) returns a number (the position) when “Baker” appears in the Supplier column, and an error when it does not. ISNUMBER converts those results to TRUE/FALSE. The formula returns the four products supplied by Baker Street Supply Co.: Magnifying Glass, Field Binoculars, Evidence Bag Kit and Brass Telescope.

Example 3: FILTER + SORT

FILTER results come back in the same order as the source data. To sort the filtered results, wrap FILTER inside SORT:

This filters Surveillance products and sorts them by column 4 (Price) in ascending order.

FILTER vs QUERY

Both FILTER and QUERY return subsets of your data, but they work differently.

Use FILTER when you need to grab rows matching conditions. It is faster to write and easier to read for straightforward filtering.

Use QUERY when you need to group, aggregate, sort, or select specific columns in one formula. QUERY handles more complex data operations in a single step.

Common Errors and How to Fix Them

#N/A: No Matching Rows

FILTER returns #N/A when no rows match all conditions. This is not a formula error, it means the filter worked correctly but found nothing.

Wrap the formula in IFERROR to return a message instead:

#N/A: Condition Size Mismatch

Each condition must have the same number of rows as the range. If A2:F9 has 8 rows but your condition references C2:C5 (4 rows), FILTER returns #N/A. It reads the same either way round: a 4-row range against an 8-row condition returns #N/A too.

Fix: make sure every condition column covers the same row range as the data.

#REF!: Spill Conflict

FILTER results spill into cells below the formula. If those cells already contain data, FILTER returns #REF!.

Fix: clear the cells below and to the right of the formula, or move the formula to an empty area.

Tips and Best Practices

1. Use cell references for conditions. Instead of hardcoding "Surveillance" in the formula, reference a cell: =FILTER(A2:F9, C2:C9=H1). Change H1 to filter by any category without editing the formula.
2. Combine FILTER with SORT, UNIQUE, or INDEX. FILTER returns an array, so you can nest it inside other functions. =UNIQUE(FILTER(B2:B9, C2:C9="Surveillance")) returns unique product names.
3. FILTER can return columns too. To filter columns instead of rows, orient the condition as a row: =FILTER(A1:F1, A2:F2>50). Be careful what you compare against, though. That formula returns five of the six headers, not just Stock, because in Google Sheets any text value counts as greater than any number, so "SKU-101">50 is TRUE. A numeric comparison across a row only behaves the way you expect when every cell in that row is a number.
4. FILTER results are dynamic. When source data changes, the filtered results update automatically. There is no need to re-run or refresh the formula.

Related Google Sheets Tutorials

• QUERY Function: Complete Guide: Text-based query language for filtering, sorting, and aggregating data
• VLOOKUP: Complete Guide: Look up a single value from a table by searching the first column
• XLOOKUP: Complete Guide: The modern lookup function with built-in error handling
• SUMIF and SUMIFS: Add values that match conditions without returning full rows