Excel 2020: VLOOKUP to Two Tables

August 13, 2020 - by Bill Jelen

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

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.