Google-sheets – Help with a “=QUERY(IMPORTRANGE(…” formula in Google Sheets

google sheetsgoogle-sheets-queryimportrange

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.

Best Answer

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)

Related Topic