Google Sheets FILTER: Keep Rows That Match
By Szabó Gergő · Updated
FILTER returns rows that meet a condition. In Sheets it is often simpler than QUERY for one or two tests, and it keeps original column order.
Syntax and arguments
=FILTER(range, condition1, [condition2, ...])- range
- The rows and columns to return.
- condition1
- A TRUE/FALSE column the same height as range.
- condition2
- Optional extra conditions; all must be TRUE.
FILTER examples
Status in C2:C100, table A2:E100.
=FILTER(A2:E100, C2:C100="Paid")Paid rows spill under the formula.
Region B, amount D, targets in G1 and G2.
=FILTER(A2:E100, B2:B100=G1, D2:D100>=G2)Each extra argument is another AND condition.
No row may match.
=IFERROR(FILTER(A2:E100, C2:C100=G1), "No rows")FILTER returns #N/A when empty; IFERROR supplies a label.
Common mistakes
Condition range different height
A2:E100 must pair with C2:C100, not C:C, unless the range is also open-ended.
OR logic as extra arguments
Extra FILTER conditions are AND. For OR, add two FILTERs with VSTACK or use QUERY.
Putting FILTER inside a column that already has values
Leave the spill area empty, same as ARRAYFORMULA.
FILTER FAQ
Is Sheets FILTER the same as Excel FILTER?
The idea is the same. Excel uses a single include array and if_empty. Sheets uses extra condition arguments and #N/A when empty.
Can I FILTER another tab?
Yes. =FILTER(Data!A2:E, Data!C2:C="Paid").
How do I sort the result?
Wrap with SORT: =SORT(FILTER(A2:C100, C2:C100="Paid"), 1, TRUE).