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 it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
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.