VLOOKUP Function - Microsoft Excel's Most Handy Tool

 Introduction to the VLOOKUP formula

 So, let me first clear up any confusion. For those of you who have never used a vlookup before, pay attention.

What is a VLOOKUP?

It is a function in Excel that is used to pull data together from two different data sets based on one common value in both data sets.

What's the formula?

=vlookup(lookup_value,table_array,col_index_num,range_lookup)

How does it work?

Well, here is my shot at trying to explain it: It works by referencing the value in a selected cell (lookup_value) and searching through the first column of the selected data range (table_array) for a value that matches the lookup_value. The VLOOKUP starts at the first row of the data range and makes its way to the last row. If no match is found in the first row of the first column in the data range, it moves to the second row. If no match is found in the second row, it moves to the third row, and so on until the end of the data range is reached. Once that reference value is found in first column of the data range, the formula returns an output value. The output value that is returned can be any column in the row where the match is found. It is based on which column you selected in the formula (col_index_num).  The output value will be displayed in the cell where you entered the vlookup formula. The last term of the formula (range_lookup) tells the function to return an output value based on a perfect match or a close one. It will make more sense once you read the example...

VLOOKUP your skirt ;)

Let's break it down.

Let’s say you have two data sets, one from Korea and one from Japan. Each contains test results of several different tests. Each “Test Results ID” represents a test that was taken. The data is on two separate tabs as shown below:

Korea Test Results

Japan Test Results (notice that there are more Japan Test Result IDs than in the Korea tab)

We want to quickly compare the test results from the tests taken in Japan versus the tests taken in Korea. In other words, let’s find the “Test Results ID”s that are the same in both data sets and put the test results side by side. This can be done quickly and easily with a VLOOKUP.

Here is the end result:

I will take you through how to get here step by step.

Step 1.         Order both Korea and Japan data sets by Test Results ID from smallest to greatest. Since we want to match Test Result ID from both data sets, this a critical step in order for the VLOOKUP to work correctly. As described in the intro section, the function starts at the first value and makes its way down. If the data is out of order it will cause the vlookup to stop looking- its hard to explain, so just order the data sets. Trust me :) 

Step 2.         Enter in VLOOKUP formula. Go to the Japan tab, and in cell D1, type in “Test Result Korea”. This is the column where we will enter the VLOOKUP and pull the Test Results from the Korea tab. In cell D2 enter in =VLOOKUP( 

Step 3.         Find your “lookup_value”. The first term of the VLOOKUP function is the value you want match with the other data set. In our case, we want to match the Test Result IDs so select B2. Then, enter a comma(,) to close the first term in the VLOOKUP. 

Step 4.               Select the “table_array”. This is the data range of the data you wish to reference in the VLOOKUP for returning an output value. You must always select the data range so that the column you want to match is the first column in the data range. Again, this is the column that will be searched, row by row. In our case, we want to match Test Result ID. Go to the Korea tab and select B2 to D11. Then, enter a comma (,) to close the term.  

Before we move on to the next step, we must enter in two dollar signs “$”. One in front of 2 (B$2) and one in front of the 11 (D$11). The reason being, it locks the data range so that the data range will not change as we copy and paste the formula down (We will do this in Step 7 below). 

 Step 5.               Select the “col_index_num”. This is where you select the column you wish to bring back. From our table_array (or data range), we have selected three column. One - being our Test Result ID, Two- being our Test Date column, and three - being our Test Result. So we want to enter “3” into our formula. Then, as always, enter a comma(,) to close the term. 

Step 6.               Select “range_lookup”. Here we are selecting whether we want a perfect match (“False”), or we want something that is similar (“True”). We want to perfectly match the Test Result IDs from Japan and Korea so enter “False”. This time we close the formula with closed parenthesis “)” and hit enter. You can see that the Korea Test Results for Test Result ID 124676 is also “F”. 

 Step 7.               Copy and paste the VLOOKUP formula. Next, we want to bring back all the Test Results for all the Test Result IDs we have. Simply copy the formula in D2 down to D15. There are a number of ways this can be accomplished:

 a. Click on cell D2. Then click the bottom right corner where the black dot is and drag down to cell D15.

 b. Copy cell D2. Then highlight cell D3 to D15 and paste.

 c. Click on cell D2. Then highlight down to D15 and press CTRL + D.

 You should end up with something like this:

 If it does, you have successfully used the VLOOKUP! The “Test Result” column from the Korea tab is now next to the “Test Result” column on the Japan tab. To learn how to remove  the #N/A, check the next step...

    Step 8.               Use the IFERROR function to replace the #N/A. When a VLOOKUP fails to match a lookup_value in the table_array, the formula will return #N/A. In our case, there were more tests done in Japan than in Korea. Test Result ID 234231, 455377, 564445 and 783231 are only in the Japan data set so we have the #N/A result. We can use the IFERROR function to replace the #N/A with another value:

 a. Go to D2 and in front of the VLOOKUP formula enter in IFERROR(

 b. After the VLOOKUP formula, enter in a comma (,) to close the first term of the IFERROR function.

 c. Next, type in what you want the output of the IFERROR function to be. In our case, let’s type in “No Korea Test”. That way, instead of seeing #N/A, you will see “No Korea Test”. Close the parenthesis “)” and hit enter.

d. Copy and paste the updated formula in D2 down to D15. You should now have something that looks like this:

 ALL DONE!

Well its truly been a pleasure. I hope this little tutorial opens up the fun world of vlookups in excel for you.

BertNort out!