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
Excel Insights – A Microsoft MVP Guide to the Best Parts of Excel

There are fewer than 100 Excel MVPs worldwide. 24 of them have contributed to this book. Written, edited, reviewed and printed by Excel MVPs, this is practical Excel passion undiluted, with each MVP highlighting some of their favorite topics.