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:
You have a separate table that includes information about the ZipCode (City, State):
Note: Table 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)
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
To further illustrate:
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:
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:
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.
Now, when you copy the formula down, you will get matches on each lookup value (as long as they appear in the lookup table)