MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: VLOOKUP to Two Tables


August 12, 2019 - by Bill Jelen

Excel VLOOKUP to Two Tables. Photo Credit: Andreas Dress at Unsplash.com

I met a person who had two price lists. The price list for the top 100 products is updated weekly. The price list for the other products is updated twice a year. They needed logic that would say, "If this product is in Table1, then use the latest price from that table. But if it is not found, then use Table2 as a backup.

The first part of the formula is =VLOOKUP(A2,$X$2:$Y$101,2,False). If the part number is found in this table, you get a price. But if the part number is not one of the top 100 products, the formula will return a #N/A error.


When you get a #N/A error, you want to do a =VLOOKUP(A2,$AA2:$AB$5000,2,False) instead.

Use the newish (Excel 2013) IFNA function to send Excel to the second table if the first VLOOKUP fails.

=IFNA(VLOOKUP(A2,$X$2:$Y$101,2,False), VLOOKUP(A2,$AA2:$AB$5000,2,False))

Title Photo: Andreas Dress at Unsplash.com


Bill Jelen is the author / co-author of
Microsoft Excel 2019 Inside Out

Dive into Microsoft Excel 2019–and really put your spreadsheet expertise to work. This supremely organized reference packs hundreds of timesaving solutions, tips, and workarounds–all you need to make the most of Excel’s most powerful tools for analyzing data and making better decisions.