Table of Contents
A cell reference can be used in a Google Sheets Query formula by placing the cell reference inside double quotation marks (“”) in the formula. The cell reference must include the sheet name and the cell coordinates, such as “Sheet1!A2”. This can be used to reference values in other sheets or cells within the same sheet that are needed for the Query formula.
You can use the following basic syntax to use a cell reference in a Google Sheets query:
=QUERY($A$1:$B$11, "Select B where A contains '"&D3&"'")
In this particular query, we tell Google Sheets to select the value in column B where column A contains whatever value is in cell D3.
The following example shows how to use this syntax in practice.
Example: Use Cell Reference in Google Sheets Query
Suppose we have the following two datasets in Google Sheets:
Now suppose we’d like to add a ‘points’ column to the right of the ‘assists’ column, which contains the points scored by each team in column D.
We can use the following syntax to do so:
=QUERY($A$1:$B$11, "Select B where A contains '"&D3&"'")
The following screenshot shows how to use this formula in cell F2 of our spreadsheet:
In this example, we told Google Sheets to select the value in column B where column A contained the team name in cell D2.
In this case, it saw that D2 contained ‘Hornets’ so it returned the ‘points’ value for Hornets, which turned out to be 78.
We can then copy and paste this formula down to every remaining cell in column F:
