Help with a "=QUERY(IMPORTRANGE(..." formula in Google Sheets

Question

Having trouble with a =QUERY(IMPORTRANGE(... formula in grabbing a value from a single cell in another sheet, but only in the row where a particular text value exists in another column in that row.

I am trying this formula:

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dMLGkP32EOoHdI6z17zyTwvn2d4MgbOU-vB2nXrE7Dc/edit#gid=1071458711","SCOREBOARD!B49:B58"),"select Col2 WHERE Col1 CONTAINS B1 ")

with hopes that when I provide my chosen text search value in a cell (B1) within the destination sheet, it will then query column 1 of the key sheet and if that text value is found, return the value from Column 2 within the range B49:B58.

I am getting an:

Unable to parse query string for function query parameter 2: NO_COLUMMN:Col2

#VALUE! error.

Answer 1

IMPORTRANGE requires authorization to access an external spreadsheet, but the authorization pop-up only is shown when IMPORTRANGE is not nested inside of other functions.

Once you make the authorization, IMPORTRANGE could be nested inside other functions like QUERY.

On the other hand, your formula includes a cell reference inside the quotes of the SQL statement. Replace "select Col2 WHERE Col1 CONTAINS B1 " by "select Col2 WHERE Col1 CONTAINS " & B1

The final formula should look like this:

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dMLGkP32EOoHdI6z17zyTwvn2d4MgbOU-vB2nXrE7Dc/edit#gid=1071458711","SCOREBOARD!B49:B58"),"select Col2 WHERE Col1 CONTAINS " & B1)

Answer 2

It looks like you need to include more columns in your importrange function. In other words, you're referencing Col1 AND Col2 in your select statement, and yet you are only importing a single column.

For the B1 reference, try ' " & B1 & " '.