Comparing Two Ranges in Excel – Part 2 Vlookup 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 recently posted out option 1 – Conditional Formatting. Here we’ll explore the Vlookup function. Check back soon for part 3, Match function.

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

Creating a Vlookup

A Vlookup will show you #N/A if the item is not on both lists. In this example, we will create a Vlookup that shows the city 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.VCell
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 Vlookup that looks for the item in the first cell of List1 and returns the city name into the cell.
a. Click in the cell where you would like to display the results (b2).
b. Type the following formula
=VLOOKUP(A2,List2,1,FALSE)Vlookup

In the formula above, a2 is the cell that has the value you are looking up; list2 is the name of the 2nd list (e2:e20), 1 is the column that will be represented (which will return the city name that is in the first column of the list), and False tells Excel that you want an exact match.

4. Create a Vlookup that looks for the item in the first cell of List2 and returns the city name into the cell.
a. Click in the cell where you would like to display the results (f2).VFilter
b. Type the following formula
=VLOOKUP(e2,List1,1,FALSE)

In the formula above, e2 is the cell that has the value you are looking up; list1 is the name of the 1st list (a2:a19), 1 is the column that will be represented (which will return the city name that is in the first column of the list), and False tells Excel 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 column 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.