Find matching values in Excel

Excel

Here's a technique to use when you have two versions of a list and need to find the differences between them based on some common unique data, like a student number or employee number. We'll use the MATCH function in a specific way to quickly identify the items that DON'T match.

We have two lists including unique userIDs, and we need to find the userIDs in the new list that don't exist in the original.

Two similar data lists in Excel

First, we're going to create a named range from the IDs in the original list. We're not naming the entire list, just the column with the userIDs. We'll call it "OrigIDs".

Naming a range in Excel

To make a named range, select the values (or the entire column) click in the Name Box, enter a name and press Enter.

Now, we'll go to the second list and insert a new column next to the ID numbers. We'll call it "Exists in OrigIDs?". This is where the MATCH function will go.

In the first cell of the new column, we'll use Formulas > Lookup & Reference > Match to start inserting the function and fill in the function arguments:

  • Lookup_value is the cell reference for the UserID in the new list, for the same row we're working on.
  • Lookup_array is the name that we assigned to the original list, "OrigIDs".
  • Match_type is 0 (zero)--this ensures that the function will look only for exact matches in the original list.

Inserting the MATCH function

Now we'll click OK and copy the MATCH function down through the remaining rows.

Results of the MATCH function in Excel

Here's how to interpret what you see:

  • Any number: the value in the revised list also exists in the original list.
  • #N/A error code: the value in the revised list was NOT found in the original list.

MATCH returns the row number / position of the value it's trying to locate in the original list. If it doesn't find the value, it'll return an error code. #N/A means "not available"--the function failed because the value it's looking for isn't available in the original list.

To list the records that do not exist in the original list, simple sort or filter the list to group the #N/A error codes together--now you can copy those lines to another worksheet.

We were reminded of this clever way to use this function from one of our students--thanks Tyler!

For more information on how to use the MATCH function, see the Excel online help.