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:
Additional Resources
The following tutorials explain how to perform other common queries in Google Sheets:
Google Sheets Query: How to Select Multiple Columns
Google Sheets Query: Select Rows that Contain String
Google Sheets: How to Query From Another Sheet