1

I try to reference another spreadsheet in the HLOOKUP formula but fail. I'm doing this like:

=HLOOKUP(A2,"https://docs.google.com/spreadsheets/d/1qOzV/","report!A2:B2",2,0)

where http... is the URL of another spreadsheet, report!... is the name of the tab in another spreadsheet and the data range.

This kind of referencing of another spreadsheet's URL works with =importrange("https://docs.google.com/spreadsheets/d/1qOzV/","report!A2:B2"), as I realized from different examples.

But with HLOOKUP while doing so I get an error:

Wrong number of arguments to HLOOKUP. Expected between 3 and 4 arguments, but got 5 arguments.

It seems, that the spreadsheet URL is counted as separate parameter.

How do I correctly reference the URL, the sheet name and the data range?

PS: I tried to chain HLOOKUP and importrange, as mentioned in https://stackoverflow.com/a/39305031/1992004 - but fail on it, got just #REF.

My formula was

=HLOOKUP(A2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1qOzV"; "report!$A$2:$B$2"),2,0)

pnuts
  • 58,317
  • 11
  • 87
  • 139
Evgeniy
  • 2,337
  • 2
  • 28
  • 68

1 Answers1

2

you are searching the content of cell A2 against imported range A2:B2 and if the match is found you returning the 1st row (of the whole dataset) of imported range after match eg. that's cell B2 of report!$A$2:$B$2. in other words range A2:B2 is one row so you cant return 2nd row from the one-row matrix, therefore valid formula is:

=HLOOKUP(A2,IMPORTRANGE("1I69LQ37hA5NjjJmuwmWkrDI_daiU_cnYDrUBpMm","report!$A$2:$B$2"),1,0)
player0
  • 124,011
  • 12
  • 67
  • 124
  • Is this the only way to do hookup from different spreadsheet - by importrange? Or is there a possibility to reference directly, like in Excel? – Evgeniy Jan 15 '19 at 12:22
  • @Evgeniy this is the only way. `1I69LQ37hA5NjjJmuwmWkrDI_daiU_cnYDrUBpMm` - this part can be either ID of your sheet or whole URL of your sheet (just fyi) – player0 Jan 15 '19 at 12:27
  • 1
    The correct formula is `=VLOOKUP(A6,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1qOzV/","report!A:B"),2,0)` - thank you! – Evgeniy Jan 15 '19 at 13:01