Microsoft Excel: Find Which Customers Are In An Already Existing List

Problem: You have a list of month to date sales by customer, as shown in Fig. 551. Your co-worker sends you a list of sales for yesterday. Figure out which customers are new so that they can be added to the list.

Strategy: Add a column to the new list. In this column, use the MATCH function. Any customers in the new list without a match in the existing list will be assigned a value of #N/A. The #N/A values can then be sorted to the bottom of the list.

The MATCH function requires three arguments. The first argument is the customer name to be looked up. The second argument is the range of existing customers. You will want to make the range an absolute address, with dollar signs in the reference. This way, the formula can be easily copied. The third argument is a zero to indicate that you are looking for an exact match.

1) Enter this formula in cell F6 =MATCH(D6,$A$6:$A$25,0). Copy the formula down to the other cells in your new list, as shown in Fig. 552.

The MATCH formula is going to return an integer that represents the relative row number where the match is found. In the present case, we don’t really care about the answer, unless a match is not found. If Excel cannot find a match, the answer will be the #N/A error. The #N/A errors will always sort to the end of a list.

2) Sort your new list ascending by column F and the new customers will sort into one spot. You can then copy and paste the new customers to the end of your existing list.

Summary: Use the MATCH function to find customers who are not in an existing list.

Functions Discussed: =MATCH()