Spreadsheet Practice

How to use a cell reference in a QUERY in Google Sheets

Google Sheets formula

=QUERY(A1:F13,"select B, D, F where C = '"&I1&"'",1)

Close the quotes and join the cell with &: =QUERY(A1:F13,"select B, D, F where C = '"&I1&"'",1). Text values keep their single quotes; numbers don't need them.

Result on the example
a 3-row list that spills from H3 in H3
Excel
See below

See it work

The formula is already in H3. Change any input and the result updates, or edit the formula to experiment.

How it works

Inside the quotes, I1 is just two characters, not a reference. So the formula builds the query text in three pieces: everything up to the opening single quote, the value of I1, and the closing single quote. The query QUERY receives is select B, D, F where C = 'West'. Change I1 and the result follows.

=QUERY(A1:F13,"select B, D, F where C = '"&I1&"'",1)

Result{3 rows × 3 cols} "Rep", "Product", "Revenue", "Raj Patel", …

  1. A1:F1313 rows × 6 cols: "Date", "Rep", "Region", "Product", …

    A range: 13 rows × 6 columns.

  2. I1"West"

    The value in I1.

  3. "select B, D, F where C = '"&I1"select B, D, F where C = 'West"

    The & operator joins as text.

  4. "select B, D, F where C = '"&I1&"'""select B, D, F where C = 'West'"

    The & operator joins as text.

  5. QUERY(A1:F13,"select B, D, F where C = '"&I1&"'",1){3 rows × 3 cols} "Rep", "Product", "Revenue", "Raj Patel", …

    QUERY: Runs a SQL-like query: select, where, group by, order by, limit, label.

    • dataA1:F1313 rows × 6 cols: "Date", "Rep", "Region", "Product", …
    • query"select B, D, F where C = '"&I1&"'""select B, D, F where C = 'West'"
    • headers1

In Excel

Excel has no QUERY function. Excel's FILTER takes the cell directly: =CHOOSECOLS(FILTER(A2:F13,C2:C13=I1),2,4,6).

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=QUERY(A1:F13,"select B, D, F where C = '"&I1&"' order by F desc",1)spills 3 × 3Different resultThe rest of the query carries on after the joined value. Largest revenue first: Kim Ortiz, then Raj Patel.

Common mistakes

  • Forgetting the single quotes around a text value gives an error, because QUERY reads West as a column name.
  • Numbers go in without single quotes: "where F > "&I2. With quotes, QUERY compares a number to text and finds nothing.
  • A value containing an apostrophe, such as O'Brien, ends the text early. Use double quotes inside the query for it: "where B = """&I1&"""".

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Use a cell reference in a QUERY

In H3, use QUERY to return the Rep, Product and Revenue of the orders for the region typed in I1.

Questions

How do I use a date from a cell in QUERY?

Format it as yyyy-mm-dd and add the date keyword: "where A >= date '"&TEXT(I1,"yyyy-mm-dd")&"'".

How do I show everything when the cell is empty?

Build the where clause conditionally: "select B, D, F"&IF(I1="",""," where C = '"&I1&"'").

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.