Microsoft Excel Help: Watch For Duplicates When Using VLookup

Here is a scenario where duplicates can cause a problem. You receive the next day’s sales in a file. When you add the MATCH function to find new customers, there is one new customer: Sun Life Finc’l, as shown in Fig. 559.

You realize that 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 F9 and paste it in cell F6. This seems like a fine solution and resolves the #N/A error in H6.

However, this will cause problems down the line. When you enter the VLOOKUP formula in column C to get the current day’s sales, there are two rows that match Sun Life Financial. The VLOOKUP function is not capable of handling this. When two rows match a VLOOKUP, the function will return the sales from the first row in the list. As shown in Fig. 560, in cell C8, the $1295 in sales is coming from cell G6 only instead of cells G6 and G8.

If you are not absolutely sure that the customers in the lookup table are unique, you should not use VLOOKUP. Instead, you would use a SUMIF formula, as shown in Fig. 561. Functions such as COUNTIF and SUMIF are explained in the next five topics.

Summary: The VLOOKUP function is excellent, but you need to be aware of the unintended problems that could be caused by having duplicates in the list.

Functions Discussed: =VLOOKUP(); =SUMIF()

Images

References and Additional Resources

If you’re looking for more tips and tutorials, check out 91 Tips for Calculating With Microsoft Excel. This collection of easy-to-follow guides shows how to customize charts and graphs, different ways to make complex spreadsheets easier to update, and even how to play games like Craps in Excel.

Other Resources:

Microsoft Excel Official Site, https://office.microsoft.com/en-us/excel/

Bill Jelen, Microsoft Excel 2010 In Depth, Available from Amazon.com.