• Excel Book Excerpt

Excel Watch for Duplicates When Using VLOOKUP

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.

LE10000455.jpg

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.

LE10000456.jpg

Figure 451 VLOOKUP returns the first match that it finds.

If you are not absolutely sure that the customers in the lookup table are unique, you should not use VLOOKUP. You could use a SUMIF function instead. See “Sum Records That Match a Criterion" on page 201 for details.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel: