Vlookup from multiple tabs at same time

[G MJ]

Hi,

I'm hoping someone can help with a problem I've encountered.

I have a worksheet with a summary tab followed by a separate tab for each year which are all formatted and set up in the same way. On the summary tab I have a very simple VLOOKUP formula looking at the date an invoice was created.

As each new year tab was created I added an iserror and added another VLOOKUP eg IF(ISERROR(VLOOKUP(A2,'Sales 2022'!a:b,2,0)),VLOOKUP(A2,'Sales 2023'!a:b,2,0),VLOOKUP(A2,'Sales 2022'!a:b,2,0))

This worked once but now we have a few years added it is no longer feasible.

What I'm hoping to achieve is some sort of vlookup which looked at multiple tabs at once eg vlookup(a2,'Sales 2022:Sales2023'!$a:$b,2,0). So if it doesn't find in tab 2021, look in 2022 if not look in 2023 and so on.

For an example each tab is set up

SELLER SIP INVOICE	SIP INVOICE DATE	SELLER INVOICE
INV-001	27/09/2022
INV-004	13/10/2022
INV-006	19/11/2022

I'm sure there is something that exists for this but my knowledge is fairly limited. Thanks.

 

[Fluff]

How about

=vlookup(a2,vstack('Sales 2022:Sales2023'!$a2:$b1000),2,0)

You need to limit the range rather than using entire columns.

 

[G MJ]

Perfect. Thank you. That works great.

That quick for something I've spent hours on.

Thanks again.

 

[Fluff]

You're welcome & thanks for the feedback.