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..
- 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.
- 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 191 for details.