Watch for Duplicates When Using VLOOKUP

April 29, 2022 - by Bill Jelen

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.

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..

A lookup table where Sun Life occurs twice in the lookup table! VLOOKUP will always return the first match it finds. — Figure 442. Is this really a new customer?

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.

As predicted, VLOOKUP returns the dollar amount associated with the first SUN LIFE record. — Figure 443. 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 for details.

This article is an excerpt from Power Excel With MrExcel

Title photo by Katharina Hermann on Unsplash