Learn How To Match Customers Using VLookup With This Microsoft Excel Tutorial

Learn How To Match Customers Using VLookup With This Microsoft Excel Tutorial
Page content

Strategy: Add a new column to the first list. Use the VLOOKUP function in the new column to grab the sales from the new list.

The VLOOKUP function requires four arguments. The first argument is the customer name to be looked up. The second argument is a rectangular range with new customer numbers in the left column of the range. In the example above, this is F4:G16. You will want to make the range an absolute address, with dollar signs in the reference: $F$4:$G$16. This way, the formula can be easily copied. The third argument is the relative column number within the existing range that you want returned. Since the sales are in column G, and G is the second column in the range F4:G16, the third argument will be 2. The fourth argument is a FALSE to indicate that you are looking for an exact match.

The formula for cell C6 is =VLOOKUP(A6,$F$6:$G$16,2,FALSE), as shown in Fig. 555. Copy the formula down to the other cells in your new list.

Look at row 12 in Fig. 556. Since we did not sell anything to this customer today, the result is #N/A. While the #N/As were useful in a previous chapter, they are fairly annoying here. The rule for calculation says that anything plus #N/A will return #N/A. When you add a new column to total column B & C, the #N/As will cause problems.

There are several methods for dealing with the #N/A cells. If you can sort your original list, simply sort by column C. All of the #N/A cells will sort to the bottom. Use the formula in column D only for the customers with sales today. In Fig. 557, you would copy D6:D16 and Paste Special Values into range C6.

Additional Details: You could also use the ISNA function to deal with VLOOKUP results that return #N/A. The ISNA function will return a TRUE if the result of a formula is #N/A. You can then use the ISNA function as the first part of an IF function. One solution is to use ISNA and IF in the calculation of the new total. As shown in Fig. 558, the new total is the previous MTD number in B6 plus C6 if it is not #N/A.

The other solution is to use the ISNA function in the original VLOOKUP formula. This is the solution that I use most, even though it requires

Excel to calculate the VLOOKUP twice. The formula becomes long: =IF(ISNA(VLOOKUP(A6,$F$6:$G$16,2,FALSE)),0,VLOOKUP(A6,$F$ 6:$G$16,2,FALSE))

Summary: You can use a VLOOKUP function to match customers in two lists.

Functions Discussed: =VLOOKUP(); =ISNA(); =IF()

See all Microsoft Excel tips

Images

Fig. 555

Fig. 556

Fig. 557

Fig. 558