Use Vlookup across multiple sheets in Google sheets

Question

I am using Vlookup formula to lookup data in V5 sheet of another google sheet, and it works perfectly. There is another tab named V7 in same google sheet which has similar data but column alignment is different. I want my vlookup function to lookup in the V7 tab as well.

=ARRAYFORMULA(If(A2:A="","",VLOOKUP(A2:A, {IMPORTRANGE("1MNRMKGkC-c0COugUWpXOe5OxJXfmdXGboxAPhf5SaLA", "V5!T3:T"), IMPORTRANGE("1MNRMKGkC-c0COugUWpXOe5OxJXfmdXGboxAPhf5SaLA", "V5!D3:D")} ,2,0)))

This is my current formula, can I somehow modify the formula to include V7 tab as well, assume the column ordering of V7 to be the same as V5. I can modify that later.

Thanks a lot in advance

There can be 2 scenarios. One, your data is spread across two sheets and so you need to lookup both sheets simultaneously. Two, you want the result of the first vlookup value to serve as the input for the second vlookup. Which one is it? — pykam
My data is spread across V5 and V7 sheets, so I need to lookup both of them simultaneously. Currently its just looking V5 sheet. V7 sheet is also in the same file as V5 sheet — Raghav Chamadiya

pykam pykam · Accepted Answer · 2020-06-01T09:53:39

If the data is spread across two spreadsheets you will need two vlookups simultaneously. You can do this by using the IFERROR or IFNA formulas. The idea is to check if the 1st vlookup returns nothing and use the 2nd vlookup in that case.

I have put a simple examples below. Hope it helps.

=IFNA(VLOOKUP(1000,A1:B10,2,FALSE),VLOOKUP(1000,A11:B20,2,FALSE))

=IFERROR(VLOOKUP(1000,A1:B10,2,FALSE),VLOOKUP(1000,A11:B20,2,FALSE))

Use Vlookup across multiple sheets in Google sheets

1 Answers