Skip to Content

Google Sheets QUERY Function WHERE Reference To Cell Value: Examples

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:

AB
1USD10.00
2AUD5.89
3USD110.00
Sheet of data containing currency and amount.

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:

AB
1USD
=QUERY(A:B, "SELECT * WHERE A = 'USD'")
10.00
2USD110.00
Using a 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:

AB
1USD
=QUERY(A:B, "SELECT * WHERE A = '"&Settings!A1&"'")
10.00
2USD110.00
Using a 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 QUERY parameter 2: NO_COLUMN

QUERY function 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.