Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
FILTER vs QUERY in Google Sheets
Heads up: Excel and Google Sheets do this differently.
Excel
=IFERROR(VLOOKUP("Active",A2:B6,2,FALSE),"Not found")Google Sheets
=QUERY(A2:B6,"SELECT A,B WHERE B='Active'",0)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Name | Status |
| Alice | Active |
| Bob | Inactive |
| Carol | Active |
| David | Active |
| Eve | Inactive |
=IFERROR(VLOOKUP("Active",A2:B6,2,FALSE),"Not found")→Not found
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1For FILTER: write =FILTER(range, condition1, [condition2, ...]) — example: =FILTER(A2:B6, B2:B6="Active") returns all rows where column B is Active.
- 2Stack conditions using * for AND and + for OR inside FILTER: =FILTER(A2:B6, (B2:B6="Active")*(A2:A6<>"")).
- 3For QUERY: write =QUERY(data_range, "SELECT * WHERE B = 'Active'") — column letters in the SQL string refer to spreadsheet columns A, B, C.
- 4Use QUERY for aggregation like SUM or COUNT: =QUERY(A2:B6,"SELECT A, COUNT(B) GROUP BY A",1).
Tips
- FILTER is easier to maintain in formulas referencing other cells; QUERY is better when you need sorting, grouping, or aggregation in one step.
Need the Google Sheets version instead? Open Sheets variant in workspace →
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Does FILTER exist in Excel?
Yes, FILTER was added to Excel 365 and Excel 2021. However, QUERY is exclusive to Google Sheets.
What happens when FILTER finds no matches?
FILTER returns a #CALC! error if no rows match. Wrap it in IFERROR or use =IFERROR(FILTER(...),'No results') to handle empty results gracefully.