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

Question

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?

Answer 1

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), ))