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
This is super useful, thank you!
Is there a way to write this such that it auto-populates column F each time new data arrives in columns A and D? Say you added Knicks stats to Row 12, is there a way to have it auto update instead of copying and pasting the formula down from F11 to F12?
I’m trying to pair this with an API that will push data, so it would be beneficial to have it populate on its own to have real-time data.
Same here thank you for this.
I saw a link in Google community forum where someone said its better you use Filter function. I was surprised it finally works with your guide.
Thank you
Same here thank you for this. I saw a link in Google community forum (https://support.google.com/docs/thread/60498393/google-sheet-query-cell-reference-problem?hl=en) where someone said its better you use Filter function.
I was surprised it finally works with your guide.
Thank you