1

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.

3

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.

By 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 as this:

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

  • Thanks Ruben....The sheet is authorized. I have just tried this: =QUERY(IMPORTRANGE("docs.google.com/spreadsheets/d/… Col2 WHERE Col1 CONTAINS '"B1"')") and it is now properly identifying my search term, but is now returning an #ERROR! Formula parse error – Mark Brockett Aug 3 '17 at 19:25
  • Does your spreadsheet use a comma or a dot a decimal separator? – Rubén Aug 3 '17 at 19:28
  • Oh, I see that you formula use B1 inside the SQL statement... – Rubén Aug 3 '17 at 19:30
0

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 & " '.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.