Comparing Two Ranges in Excel – Part 3 Match Function

At the PMI Professional Development Day event, our instructors were asked for technology tips for various platforms. We wanted to share one of those questions and three tips for how to complete the action. The question was in regards to comparing two ranges in Excel. We have shared two ways to do this in previous blog postings – Conditional Formatting and Vlookup function. Here we’ll explore the Match function.

Please use this Excel template file for the practice exercise below.  This Excel file is for the results.

Using the Match Function

The Match function will show you #N/A if the item is not on both lists. Where the Vlookup will show you the name of the city, Match will tell you what row the city is in on the corresponding list. In this example, we will create a Match function that shows what row the city is in if it is in both lists or returns the error message #N/A if the city is not in both lists. We will create the named ranges List1 and List2.

1. Select the first range of cells and name it List1.MatchCell
a. Highlight a2:a19
b. Click in the Name Box. Type List1 and press Enter. (It is important that you not place a space between List and the number 1 when naming a range.)

2. Select the second range of cells and name it List2.
a. Highlight e2:e20
b. Click in the Name Box. Type List2 and press Enter.

3. Create a Match function that looks for the item in the first cell of List1 and returns the row where the city may be found in List2.
a. Click in the cell where you would like to display the results (c2).
b. Type the following formula
=MATCH(A2,List2,0)MatchFunction
In the formula above, a2 is the cell that has the value you are looking to match; list2 is the name of the 2nd list (e2:e20), 0 represents that you want an exact match.

4. Create a Match function that looks for the item in the first cell of List2 and returns the row where the city may be found in List1.
a. Click in the cell where you would like to display the results (g2).
b. Type the following formula
=MATCH(e2,List1,0)
In the formula above, e2 is the cell that has the value you are looking to match; list1 is the name of the 1st list (a2:a19), 0 represents that you want an exact match.

Note: All the cells with #N/A represent the city that is not in your list in the adjacent columnMatchFilter to the left. You can filter each list individually to show what cities are not represented. Right-click a cell in the first table that contains #N/A. Select Filter by Selected Cells Value. You will need to Clear this filter before filtering the second table.

Excel is a very powerful tool and there are lots of options for looking at the data the way you need to see it. We have plenty of tips and tricks to show you for this tool and other Microsoft Office products. Check out the Excel courses we offer or view our entire Class Catalog.