Excel VLOOKUP – EXACT Match

Excel’s VLOOKUP function is a very powerful tool that allows users to combine and match two columns in separate datasets.  When performing a VLOOKUP, a given value is matched in a separate range of values within a specific column, returning a value (the desired lookup value) specified in the column index of the row of the match.  The V in VLOOKUP stands for “Vertical” column.  A good example of how the VLOOKUP function operates is looking up a number in a phone book, or scanning a menu to determine the price of a food item.  First, you scan through the list of items to find the value (phone number or food item) you are looking for (top to bottom), then you move your finger to the right to find the associated value (phone number or price) of the item you were looking for.

In this post, I will provide a detailed example of how the Excel VLOOKUP function operates, using the EXACT match criteria.

To view how the VLOOKUP Approximate match works, you can go to the following post:     Excel VLOOKUP – Approximate Match

Excel VLOOKUP – EXACT Match

Let’s say you have the following table, with employee name, zip code and population:

Table A

Excel VLOOKUP

You have a separate table that includes information about the ZipCode (City, State):

Table B

Excel VLOOKUP

NoteTable data can be found at the very bottom of the post

Now, you would like to add “City” to Table A above.  Here is where the VLOOKUP comes into play.  In cell D2 of Table A, you would enter the following formula:

In Cell D2, enter:      =VLOOKUP(B2,F1:H11,2,FALSE)

Excel VLOOKUP

Here is a breakdown of the formula:

  • B2 – This is the value the formula should use to lookup information in the second table (i.e. lookup ZipCode “14001” in Table B, Column F)
  • F1:H11 – This is the range the formula will scan for the ZipCode value “14001”, and it will lookup this value in Column F
    • Important Note:  In the lookup table (Table B above), the column you are looking for the match should always be the LEFT most column (Column F in this case)
  • 2 – This is the column index of the lookup table (F1:H11 – Table B above) that will be returned in the formula result, once a match has been made (in this example, we want the “City”)
  • FALSE – This tells the formula to use an “EXACT Match” – If no match is found, an error will be returned

Formula Result:

Excel VLOOKUP

To further illustrate:

Excel VLOOKUP

The formula looked up value in cell B2 of Table A “14001” in range F1:H11 (Table B), scanning Column F for the match.  It found ZipCode “14001” in row 8, cell F8.  Then, the formula returned the column index “2” of the lookup table, which was the City “AKRON”.  This was an EXACT match for ZipCode “14001”.

At this point, you would naturally copy the formula in cell D2 down for the rest of the rows to get the matching City:

Excel VLOOKUP

Notice that some of the formulas return the “#N/A” error.  This is a common mistake made when copying the VLOOKUP formula for down for multiple cells.  If you highlight the formula in cell D4, it now looks like this:

Excel VLOOKUP

When you copied the formula down, the “Lookup Table Range” automatically adjusted to “F3:H13”.  So in our example, the zip code “11714” was not found in Table B, because the formula started looking in column F at row 3, missing the match.

To solve this problem, you should use either a Named Range or an absolute lookup cell range.

The quickest way is in your original formula, highlight the “Lookup Table Range” in the formula editor and press the F4 key.  This will make the cell range absolute (will not change) as you copy the formula down to lookup the rest of the ZipCode values.

Excel VLOOKUP

Now, when you copy the formula down, you will get matches on each lookup value (as long as they appear in the lookup table)

Excel VLOOKUP

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

Table A

NameZipCodePopulation
Jeff1400125,000
Lauren12201150,000
Mindy11714200,000
Rich14203350,000
Kelly1405245,000
John1473110,000
Matt1252475,000
Mary1294623,000
Don1342011,000
Kim129837,500

 

Table B

ZipcodeCityState
11714BETHPAGENY
12201ALBANYNY
12524FISHKILLNY
12946LAKE PLACIDNY
12983SARANAC LAKENY
13420OLD FORGENY
14001AKRONNY
14052EAST AURORANY
14203BUFFALONY
14731ELLICOTTVILLENY

 

 

Leave a Comment