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

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.

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

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G | H | I |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Date | Rep | Region | Product | Units | Revenue |  | Region | West |
| 2026-07-03 | Ana Ruiz | East | Widget | 40 | 2080 |  |  |  |
| 2026-07-06 | Raj Patel | West | Gadget | 25 | 2225 |  |  |  |
| 2026-07-10 | Lee Chen | East | Gizmo | 12 | 1740 |  |  |  |
| 2026-07-14 | Ana Ruiz | North | Gadget | 30 | 2670 |  |  |  |
| 2026-07-17 | Kim Ortiz | West | Widget | 55 | 2860 |  |  |  |
| 2026-07-21 | Raj Patel | East | Widget Pro | 18 | 1422 |  |  |  |
| 2026-07-24 | Lee Chen | South | Gadget | 22 | 1958 |  |  |  |
| 2026-07-29 | Kim Ortiz | East | Gizmo | 9 | 1305 |  |  |  |
| 2026-08-04 | Mo Diaz | North | Gizmo | 14 | 2030 |  |  |  |
| 2026-08-07 | Raj Patel | South | Widget | 20 | 1040 |  |  |  |
| 2026-08-12 | Kim Ortiz | North | Gadget | 16 | 1424 |  |  |  |
| 2026-08-18 | Mo Diaz | East | Gizmo Mini | 28 | 1092 |  |  |  |

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

Result (spills 3 × 3): Rep, Product, Revenue; Raj Patel, Gadget, $2,225; Kim Ortiz, Widget, $2,860

## 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.

## Other ways

- `=QUERY(A1:F13,"select B, D, F where C = '"&I1&"' order by F desc",1)`: The 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&""""`.

## 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&"'")`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/use-a-cell-reference-in-query