When a value could be in either of two tables, nest two VLOOKUPs in IFERROR: if the first table returns #N/A, Excel looks in the second. In Excel 365, XLOOKUP with VSTACK does it in one lookup.
The problem
A distributor’s weekly sales list has product codes, but prices are in two separate tables, one for Cadbury and one for Mars.
A VLOOKUP on the Cadbury table returns #N/A for every Mars code, and the other way round.
Method 1: IFERROR with two VLOOKUPs (any version)
=IFERROR(VLOOKUP(C3,$K$4:$M$7,3,FALSE),VLOOKUP(C3,$K$11:$M$15,3,FALSE))If the first VLOOKUP finds the code, its price is used. If it fails, IFERROR runs the second VLOOKUP on the Mars table.
For a third table, nest again: =IFERROR(VLOOKUP(...),IFERROR(VLOOKUP(...),VLOOKUP(...))). Use IFNA instead of IFERROR to hide only not found, not typing mistakes.
Method 2: XLOOKUP with VSTACK (Excel 365)
VSTACK joins the two tables into one list, so a single XLOOKUP searches both:
=XLOOKUP(C3,VSTACK($K$4:$K$7,$K$11:$K$15),VSTACK($M$4:$M$7,$M$11:$M$15),"Not found")Add a third table by adding one more range to each VSTACK. If the price tables are Excel Tables, use their names, such as VSTACK(Cadbury[Code],Mars[Code]), and new products are included automatically.
Better long term: one price list
If you control the workbook, keep all products in one table with a Company column. One lookup is faster, easier to check, and works in every version. Power Query’s Append can combine the two tables into one and refresh when either changes.