Search code examples
google-sheetsvlookupimportrange

Why does VLOOKUP only works once per row?

I have a VLOOKUP & IMPORTRANGE formula that works great on the first cell I use it on. When I try to use it on another cell the IMPORTRANGE fails (I assume) and I get an error:

Error Did not find value '21' in VLOOKUP evaluation.

The INDIRECT function is working and the error states the correct value I am searching for.

Code:

=VLOOKUP(INDIRECT("B" & ROW()),IMPORTRANGE("XXX","YYY!A1:E500"),5,false)

It should work on multiple rows, but it only works on the first row. Is there a Sheets limitation I am not aware of?

Solution

  • continuity is secured by ARRAYFORMULA. try:

    =ARRAYFORMULA(VLOOKUP(INDIRECT("B" & ROW()), IMPORTRANGE("URL-ID", "YYY!A1:E500"), 5, 0))

    you may want to wrap it in IFERROR() to avoid #N/A!