The FILTER function in Google Sheets returns only the rows or columns that meet your conditions. It creates a live result that updates when the source data changes.
Use it for thresholds, exact text, multiple conditions, partial text, or a criterion stored in another cell. The examples below use one tested task table.
FILTER Function Syntax in Google Sheets
=FILTER(range, condition1, [condition2, ...])
- range is the data FILTER returns.
- condition1 is a TRUE/FALSE test aligned with the range.
- condition2, … adds optional tests. Separate conditions work as AND logic.
FILTER is useful when you need a changing subset without sorting, deleting, or copying source rows. It can return a whole table or selected columns.
The examples use A1:D7 with Task, Team, Amount, and Status columns. Data rows include Sales, Finance, and Support tasks.
| Task | Team | Amount | Status |
|---|---|---|---|
| Report | Sales | 120 | Open |
| Budget | Finance | 250 | Done |
| Review | Sales | 180 | Open |
| Invoice | Finance | 90 | Done |
| Forecast | Sales | 310 | Open |
| Sales notes | Support | 75 | Open |
How to Use the FILTER Formula
- Click an empty cell with enough clear space below and to the right.
- Type
=FILTER(and select the range you want returned. - Add a comma, then enter a condition that covers the same rows.
- Add more conditions when every test must pass, then close the parenthesis.
- Press Enter. FILTER expands the matching records from the formula cell.
You enter FILTER once. It returns an array, so you do not copy the formula down each row.
Each condition creates a TRUE/FALSE result for every source row. FILTER preserves rows marked TRUE and leaves their source order unchanged.
Filter Rows by a Number or Exact Text
Filter amounts at or above 180
=FILTER(A2:D7,C2:C7>=180)
The condition checks every amount in C2:C7. It returns Budget at 250, Review at 180, and Forecast at 310.
Filter rows with an exact text match
=FILTER(A2:D7,B2:B7="Sales")
This formula returns Report, Review, and Forecast because their Team cells equal Sales. Put literal text inside double quotation marks.
Exact equality keeps Support and Finance rows out, even when a task name contains the word Sales. The condition checks column B only.
To extract selected columns from a result, pair FILTER with the CHOOSECOLS function.
Use Multiple FILTER Conditions with AND or OR
Require every condition with AND
=FILTER(A2:D7,B2:B7="Sales",C2:C7>=180)
Separate condition arguments act as AND logic. Review and Forecast remain because each row belongs to Sales and has an amount of at least 180.
Accept either condition with OR
=FILTER(A2:D7,(B2:B7="Sales")+(B2:B7="Support"))
Adding the tests creates OR logic. The formula returns the three Sales tasks and the Sales notes task assigned to Support.
Filter Text That Contains a Word
=FILTER(A2:D7,ISNUMBER(SEARCH("sales",A2:A7)))
SEARCH looks for “sales” inside each task. ISNUMBER converts each found position into TRUE, so FILTER returns the Sales notes row.
SEARCH is case-insensitive. This makes the formula useful when capitalization varies across imported or manually entered text.
The formula searches Task values in column A, then returns all four columns from matching rows. Partial-text matching and returned columns can come from different ranges.
Use a Cell as the FILTER Criterion
Enter Sales in H2, then use this formula:
=FILTER(A2:D7,B2:B7=H2)
The result contains Report, Review, and Forecast. Change H2 to Finance or Support, and the returned rows update without editing the formula.
Return One Column Based on Another
=FILTER(A2:A7,B2:B7="Sales")
The return range contains only Task cells, while the condition checks Team cells. The output is Report, Review, and Forecast in one column.
The return range and condition range may use different columns. Their corresponding dimensions must still align.
This pattern is useful for clean validation lists, assignment queues, or names-only reports when the decision field sits elsewhere in the table.
Handle FILTER Headers, No Matches, and Errors
Choose whether to include headers
FILTER returns whatever you include in its range. Formulas using A2:D7 exclude row 1, while this tested formula includes it:
=FILTER(A1:D7,A1:A7<>"")
The result begins with Task, Team, Amount, and Status. FILTER does not automatically remove or create headers.
Replace the no-match error
=IFNA(FILTER(A2:D7,B2:B7="Legal"),"No matches")
A FILTER for Legal produces #N/A because the fixture has no Legal rows. Wrapping it in IFNA displays “No matches” instead.
IFNA handles the expected no-result case without hiding unrelated errors. That makes a missing match distinguishable from a malformed formula or blocked output.
Keep condition ranges aligned
=FILTER(A2:D7,B2:B6="Sales") produces #N/A. The return range has six data rows, while the condition checks only five.
For more complex selecting, sorting, grouping, and totals in one formula, compare the QUERY function in Google Sheets.
Other Google Sheets articles you may also like