FILTER Function in Google Sheets
Returns a filtered version of a source range that satisfies one or more conditions. The simplest way to extract matching rows without writing a QUERY string.
Syntax
FILTER(range, condition1, [condition2, ...])Returns: A range containing only the rows where all conditions are TRUE.
Excel equivalent: FILTER (identical, requires Excel 2021+ or Microsoft 365)
Parameters
| Name | Required | Description |
|---|---|---|
| range | Required | The range to filter. |
| condition1, condition2, ... | Required | Boolean arrays the same length as range. Each row of range is kept only if ALL conditions are TRUE for that row. |
Examples
Filter by single condition
=FILTER(A2:C100, B2:B100 > 1000)Returns columns A, B, C for rows where B is greater than 1000.
Multiple AND conditions
=FILTER(A2:E100, B2:B100 = "West", C2:C100 > 0)Rows where column B is "West" AND column C is positive. Each condition is a separate argument; FILTER treats them as AND.
OR logic via boolean math
=FILTER(A2:E100, (B2:B100 = "West") + (B2:B100 = "East"))+ acts as boolean OR. Returns rows from West OR East. For AND across multiple OR groups, use parentheses carefully.
When to use an alternative
- QUERY — You need grouping, aggregation, or column reordering alongside the filter.
- ARRAYFORMULA + IF — You want to return the original range size with blanks for non-matching rows instead of a shrunken result.
- FILTER + SORT — You need filtered AND sorted output — chain them.
Common errors and how to fix them
#N/A "FILTER has no matches"
Cause: No rows satisfy the conditions.
Fix: Wrap with IFERROR for a fallback: IFERROR(FILTER(...), "No matches").
Condition arrays don't match range size
Cause: Range is A2:C100 (99 rows) but condition is B2:B50 (49 rows).
Fix: Make condition arrays exactly the same length as the filtered range.
Related functions
Frequently Asked Questions
What's the difference between FILTER and QUERY?
FILTER is simpler for pure row filtering with AND/OR conditions. QUERY adds SELECT (column reordering), GROUP BY (aggregation), ORDER BY (sorting), LIMIT, and a SQL-like syntax. Use FILTER when you just want matching rows; QUERY when you need to reshape the output.
Can FILTER use OR conditions?
Yes, with boolean math. Conditions joined by + act as OR: FILTER(A:C, (B:B="X") + (B:B="Y")). Conditions as separate arguments are AND. You can combine: FILTER(A:C, ((B:B="X") + (B:B="Y")) * (C:C > 0)).