Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgs-vlookupimportrange

Combination of VLOOKUP and IMPORTRANGE in an array formula doesn't work

I would like to import some data with the combination of ARRAYFORMULA, VLOOKUP, and IMPORTRANGE like this:

=arrayformula(vlookup(A3:A,importrange(T3:T,"sheet1!A:AA"),21,false))

in column T, there are individual sheet URLs.

However, this doesn't work because the formula only uses sheet URL in T3 than in another row other than row 3, it says #N/A because the value in A4 doesn't appear on a sheet URL in T3, where sheet URL must be the one in T4.

How to correct the formula above?

Solution

  • IMPORTRANGE does not support array range reference input.

    the workaround would be to use multiple IMPORTRANGEs in an array like:

    =ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, {IMPORTRANGE(T2, "sheet1!A:AA");
                                     IMPORTRANGE(T3, "sheet1!A:AA");
                                     IMPORTRANGE(T4, "sheet1!A:AA")}, 21, 0), ))