This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Watch for Duplicates When Using VLOOKUP
Problem: I used the VLOOKUP function to get sales from a second list into an original list, and then I received the next day’s sales in a file. When I use the MATCH function to find new customers, there is one new customer: Sun Life Fincl.
Figure 450 Is this really a new customer?
This is not really a new customer at all. Someone in the order entry department created a new customer instead of using the existing customer named Sun Life Financial. As a quick fix, you copy cell D9 and paste it in cell D6. This seems like a fine solution and resolves the #N/A error in F6.
However, when I enter the VLOOKUP formula in column C to get the current day’s sales, there are two rows that match Sun Life Financial.
Strategy: It’s important that you understand how VLOOKUP handles duplicates in the lookup list. The VLOOKUP function is not capable of handling the situation described here. When two rows match a VLOOKUP, the function will return the sales from the first row in the list. You will get the $3541, but you will not get the $2815.
Figure 451 VLOOKUP returns the first match that it finds.