How do you reference a cell in the
WHERE
clause of a Google Sheets
QUERY
function?
To reference a cell in the
Google Sheets’
QUERY
function
WHERE
clause
, simply break the
query
string by closing with a double-quoted string
"
append the concatenation symbol
&
then reference the cell append the
&
to open up the
query
string again
"
so you can continue writing the rest of your
query
.
Do be mindful it does depend on the type of value being referenced in the cell as well. If the cell reference is a string, you will need to make sure you wrap the cell reference in single quotes .
For example, suppose I had the following
Data
sheet:
| A | B | |
|---|---|---|
| 1 | USD | 10.00 |
| 2 | AUD | 5.89 |
| 3 | USD | 110.00 |
If I wanted to create a
QUERY
function on this data set and I wanted to filter the rows where column
A
is the currency
USD
, using the
QUERY
function with a
WHERE
clause would look like this:
=QUERY(A:B,"SELECT * WHERE A = 'USD'")
If this
QUERY
were placed on a separate
Report
sheet (which would be highly encouraged when interacting with raw data), the result would look like this:
| A | B | |
|---|---|---|
| 1 |
USD
=QUERY(A:B, "SELECT * WHERE A = 'USD'")
|
10.00 |
| 2 | USD | 110.00 |
QUERY
with
WHERE
clause filters the rows according to the condition.
QUERY
With
WHERE
Clause Referencing Cell
So the current example is good if you just wanted a report on the
USD
transactions in your data set, but what if the requirements change and you need to report on
AUD
or on other currency transactions?
You could create another sheet and label this
Settings
with cell
A1
in this sheet being the currency to report transactions on. Therefore, in the
Report
sheet you can modify the
QUERY
function to reference this cell instead of hardwiring the required currency into the formula.
Here’s how your
Report
sheet would look:
| A | B | |
|---|---|---|
| 1 |
USD
=QUERY(A:B, "SELECT * WHERE A = '"&Settings!A1&"'")
|
10.00 |
| 2 | USD | 110.00 |
QUERY
with
WHERE
clause that references a cell.
As you can see from the above result, the output is still the same – and it should be.
Settings!A1
contains the value
USD
. But notice in the new formula that
USD
changed to be the actual reference
Settings!A1
– this is all that is needed to reference a cell’s value within your
query
statement.
#VALUE!
QUERY
Error
A common mistake when creating your formula using the
QUERY
function when referencing external cell values could be something like this:
Unable to parse query string for Function
QUERYparameter 2:NO_COLUMNQUERYfunction error
If you get this error, don’t fret it’s a sign you’ve likely forgotten to wrap your string reference in single quotes. Just remember your
query
statement is opened with double quotes, and when you are referencing a value that is to be assessed as a string, you need to open a single quote before closing the double quotes and inserting your cell reference.
How To Reference A String In
WHERE
Clause
Your
query
statement when referencing a string should open with a single quote…
"SELECT * WHERE A = '… before closing the double quotes…
"SELECT * WHERE A = '"… append your cell reference (remembering to add the concatenation symbol at the end)…
"SELECT * WHERE A = '"&Settings!A1&… open with double quotes again…
"SELECT * WHERE A = '"&Settings!A1&"… and close the single-quoted string…
"SELECT * WHERE A = '"&Settings!A1&"'
… and you can continue with the rest of your
query
string. Otherwise, if you’ve finished close with a double-quoted string…
"SELECT * WHERE A = '"&Settings!A1&"'"If it helps write the actual string the cell value contains first before swapping it out with the cell reference.
Summary
The
QUERY
function
WHERE
clause can enable you to filter your data sheet by only working on a subset of that data. If your requirements on filtering are likely to change your
query
statement in the
QUERY
function can reference cell values.
To reference cell values in a
QUERY
function insert a cell reference into the string just as you normally would with any string in a spreadsheet: insert double quotes to close the current
query
string, append the concatenation symbol
&
, insert the cell reference (i.e.
Settings!A1
), append another concatenation symbol
&
, and then open the double quotes again to continue the
query
statement on.
