Excel 2019: VLOOKUP to Two Tables
August 12, 2019 - 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
Use the newish (Excel 2013) IFNA function to send Excel to the second table if the first VLOOKUP fails.
Title Photo: Andreas Dress at Unsplash.com