VLOOKUP in multiple ranges Google Sheets

1
votes

I have a VLOOKUP formula to lookup from a different sheet as below:

=ARRAYFORMULA(IFERROR(VLOOKUP(B2:B6,Sheet3!A2:C,2,0)))

I would now like to expand the data on Sheet 3 but would like the next batch of data to be in range E2:G. Is it possible to have a VLOOKUP across 2 different ranges like this?

I've done lots of google searching without any joy tonight so thought I'd ask the question on here to make sure I'm not trying the impossible.

1
google-sheetsgoogle-sheets-formulavlookup
The solution offered by player0 below looks good. However, if you are only searching as far as column 2, you really don't need to include THREE columns in the search ranges. That is, instead of A2:C, you only need A2:B (likewise, E2:F). - Erik Tyler
Hi Erik, yes that makes sense but I am also using a VLOOKUP in another column which searches to the 3rd column of my range so it was just easier to use the full range in both columns for my own ease of reading :) - The_Train

1 Answers

2
votes

try:

=ARRAYFORMULA(IFNA(VLOOKUP(B2:B6, {Sheet3!A2:C; Sheet3!E2:G}, 2, 0)))