Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to use cell references inside QUERY criteria in Excel and Google Sheets
Topic:QUERY (Google Sheets SQL)Heads up: Excel and Google Sheets do this differently.
Excel
=FILTER(A2:C10,B2:B10=E2)Google Sheets
=QUERY(A1:C10,"SELECT A, B, C WHERE B = '"&E2&"'",1)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
- 1Type your filter criterion in a standalone cell — for example, put 'North' in E2 so users can change it without editing the formula.
- 2In your destination cell, start the QUERY formula: =QUERY(A1:C10,
- 3Add the query string with concatenation: "SELECT A, B, C WHERE B = '"&E2&"'". The single quotes inside the string wrap the cell value for text comparisons.
- 4Close the formula with the headers argument: ,1).
- 5For numeric criteria omit the single quotes: "SELECT A, B, C WHERE C > "&F2, where F2 holds a number like 1000.
Tips
- For text values, wrap the cell reference with single quotes inside the string: '"&cell&"'. For numbers, drop the quotes entirely: "WHERE C > "&cell.
- For date values, use the date literal format: "WHERE A > date '"&TEXT(G2,"yyyy-mm-dd")&"'". QUERY requires dates in yyyy-mm-dd format inside the query string.
- If the cell might be empty, wrap the whole QUERY in IFERROR or add an IF check to avoid a broken query string: =IF(E2="","(all)",QUERY(...)).
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
Why does my QUERY return an error when I reference a cell with text?
You most likely forgot the single quotes around the cell reference inside the query string. Use 'WHERE B = '"&E2&"'' (note the single quotes flanking the concatenated cell value).
Can I reference a range of cells for an IN-style filter?
QUERY does not have an IN operator. To filter against a list in E2:E5, build the OR string dynamically with a helper formula like TEXTJOIN(" OR B = '",TRUE,E2:E5) and concatenate that into the WHERE clause.
Does the same concatenation pattern work for QUERY with IMPORTRANGE?
Yes. Wrap the concatenated query string around IMPORTRANGE exactly as you would for a sheet range: =QUERY(IMPORTRANGE("url","Sheet1!A:C"),"SELECT Col1 WHERE Col2 = '"&E2&"'",1).
More on QUERY (Google Sheets SQL)
See all →How-to
How to use QUERY to filter data
Use Google Sheets QUERY with a WHERE clause to filter rows dynamically — Excel users can use FILTER as the equivalent.
Error fixFix QUERY error
QUERY parse error in Google Sheets means the SQL-like query string has a syntax mistake. Find the exact error and fix it here.
How-toHow to use QUERY to sum by group
Use Google Sheets QUERY with GROUP BY and SUM to aggregate data by category — use SUMIF for the Excel equivalent.
How-toHow to use QUERY to group by multiple columns
Use QUERY GROUP BY with multiple columns in Google Sheets to create multi-level summaries. Excel alternative uses SUMIFS with multiple criteria ranges.
How-toHow to use QUERY with WHERE, AND, OR
Filter QUERY results in Google Sheets using WHERE with AND and OR conditions. Excel users can replicate this with FILTER using * for AND and + for OR.
How-toHow to create a running total with QUERY
Build a running total in Google Sheets by combining QUERY for ordering with ARRAYFORMULA and SUMIF. Excel alternative uses a simple SUMIF with a locked anchor row.