Using The VLOOKUP Function In Microsoft Excel

​This Tech Tip was submitted by Dale Amburgey, Assistant Director, Institutional Research, Embry-Riddle Aeronautical University.

The VLOOKUP function in Microsoft Excel is one of the most powerful tools to assist with data analysis and aggregation, but it is often misunderstood. VLOOKUP allows you to search for a value from your initial dataset and return a value in another dataset based on the initial value. You can utilize VLOOKUP by inserting it from the function button or by manually entering the formula as demonstrated in this example. 

The VLOOKUP formula consists of the following: 

VLOOKUP (lookup_value, table_array, col_index_num, [range of lookup]) 

Let’s say we have two Excel workbooks: one containing a list of names and IDs and the other containing e-mail addresses and academic major.  Our task is to merge the data fields into one worksheet.  In order to do this, we will utilize the VLOOKUP.   


Let’s begin by adding the e-mail address from Worksheet #2 to Worksheet #1. First, we must ensure that we have an identifying data element in each data set. In this example, the ID number serves as the identifying feature. We then enter the VLOOKUP formula in cell D2. 


 

The formula is looking for the value in cell A2 from Worksheet #1 in the table_array in Worksheet #2 defined as $A$2:$C$11. It is important to remember that when using the VLOOKUP, the identifying data element must be in the first or leftmost position. In this example, the formula is looking for the ID from Worksheet #1 in the first column of the data_array in Worksheet #2 and will return the value that is in the second position if there is an identical match.   

After entering the VLOOKUP formula and filling down, your worksheet should appear as the following: 


And your results should look like this: 


Now, to add the major, we do the same steps for cell E2. 

Notice how the table_array has remained the same, but the col_index_num has changed from the “2” we used in the e-mail formula to “3” so that the major can be added. And, because we have indicated FALSE, we know that we are looking for an exact match based on ID.   

Our results after adding the major should appear like this.

Note how cells E2 and E10 have a value of “0.”  This occurred because our original data set had blank values for the majors associated with the respective IDs.  

If an ID number is not found in the table_array, an error value will be returned.  To demonstrate this, we will change A.C. Slater’s ID number and show the results.
 

When we changed the ID number, the #N/A error occurred because the lookup value is not contained within the table_array. 

The VLOOKUP is a function that can save you a tremendous amount of time comparing multiple data sets. It is a function for which its application grows in scope the more it is utilized. Also, keep in mind the following tips as you begin to explore the wonders of the VLOOKUP:

  • Ensure the lookup values are the same type. If your lookup value is a number in one data set and a number stored as text in the other, you will get an error.

  • When using the VLOOKUP within worksheets in the same workbook, make sure that the table_array is defined by an absolute cell reference. You want to see “$” in front of the column and row reference constant.

  • If there are duplicate identification values in your table_array, the VLOOKUP will return values based on the first instance of that value.

  • You will want to use the TRUE range of lookup in your formula when finding an exact match is not imperative and the closest match will suffice.

 

 

 Comments

 
To add a comment, Sign In
Total Comments: 18
 
Terry posted on 10/9/2013 4:52 PM
VLOOKUP is my number one formula. So often the data is a little messy and VLOOKUP makes it that much clearer. As the examples show it can also be helpful finding missing values.
Kendra posted on 10/9/2013 4:54 PM
I have found this helpful. I enjoy using VLOOKUP & HLOOKUP functions very much and this tech tip does a wonderful job explaining them! This has helped me to understand the purpose and syntax of these functions.
Lisa posted on 10/9/2013 5:04 PM
V-lookup is a very handy way to merge data from different worksheets--and a real time-saver. It can be used instead of re-writing SQL queries. To save a few keystrokes, you can use 1 or 0, instead of typing True or False. We use it almost daily in our IR office, and we always be sure to copy/paste the results over themselves so you will not lose any data if you remove the table array at a later time.
Marlene posted on 10/9/2013 5:15 PM
VLOOKUP is a very useful function in Excel and a great topic for a Tech Tip! I would be curious to learn how people use HLOOKUP as well. I don't often use that Excel function and perhaps it is just the nature of the work we do in IR.
Gary posted on 10/9/2013 5:23 PM
If you have to use Excel, this is an essential function. Don't forget it's sibling hlookup. It's easy to get burned with this one if you don't use absolute cell references, or if you add data onto the reference range but don't update the formula, or are trying to match on different data types that appear identical, and so on. Back when I did a lot of this, I developed the habit of toggling formula view often with (Ctrl + "~"); that's a life saver.
Also consider that when using range lookup = TRUE (approximate match), the table_array range must be sorted in ascending order to work properly. Thanks for the useful tip!
Julia posted on 10/9/2013 5:52 PM
This is a very easy-to-understand example of how VLOOKUP is used. One mistake I have made is not being in the correct cell when using this command, the visuals in this example explain this very well. Great tip!
Mary Jo posted on 10/9/2013 6:24 PM
Great tech tip as VLOOKUP is a very powerful function in Excel. If you did not like the #N/A for the error message, you could nest this within the IFERROR Function to put a more descriptive error message. An example would be: =IFERROR(VLOOKUP($A2, 'C:\Users\amburged\Desktop\[E-mail and Major.xlsx]E_mail and Major'!$A$2:$C$11, 2, FALSE), "Student Does Not Exist").
George posted on 10/9/2013 8:28 PM
Oddly enough, I just introduced one of my employees to vlookup. It has been a faithful friend. A word of warning for those beginning to use it: It is very resource intensive, so once you have used it and the data you aggregate can remain static, make sure to copy the new fields and paste over them with just the values. Constantly re-calculating vlookups can bring the mightiest of computers to a screeching halt.
Shabnam posted on 10/9/2013 9:32 PM
Great tip, it is something I use for data cleaning and merging different worksheet. HLOOKUP function is equally handy tool. However, I have found that constantly using Vlookup does slow Excel and sometimes hangs the computer.
Christina posted on 10/10/2013 7:39 AM
Great tech tip. VLOOKUP is a very powerful formula and you have done a great job explaining how to use it. I use this often for data checking.
Claire posted on 10/10/2013 8:03 AM
This looks like a useful formula, I'll have to try it out sometime. The examples work well for this, and I like how the formula is broken down to describe the different sections.
Lee posted on 10/10/2013 8:24 AM
Thanks Dale for a good Tech Tip. I've used the VLOOKUP function extensively and have found it to a useful way to merge data in Excel worksheets. If you're not familiar with this function, I definitely recommend that you take a look at it.
Pam posted on 10/10/2013 8:38 AM
Awesome explanation of vlookup. I also find that sometimes, particularly with older database systems, that there will be a string of spaces before or after data in a cell. A problem then arises with vlookups if the cell you are matching to does not have the spaces. To remedy this problem you can use the excel function trim which takes the extra spaces off the ends of a cell. That is: TRIM(A2). You can embed this in the center of your vlookup formula.

For example:

VLOOKUP (TRIM(A2)), table_array, col_index_num, [range of lookup])
Natalie posted on 10/10/2013 11:35 AM
This is very helpful! We also use it, especially when dealing with data from multiple sources. And thanks also for the Saved by the Bell references.
Annie posted on 10/15/2013 1:38 PM
This is a very good tip for looking items up. I used this to match advisors to program in sheet that had over 2000 rows and it was such a time saver.
Brian posted on 10/17/2013 8:55 AM
You've just been hired to your first IR job. Whether you are a research analyst, or Director of IR, the following applies:

day 1: paperwork, meet with HR, put stuff in your desk, get email going.
day 2: meet people around campus you didn't meet in the interview (Deans, Directors, etc.).
day 3: learn to use VLOOKUP.
Robert posted on 2/27/2014 4:21 PM
Thanks for the tip. I like how you used names from "Saved by the Bell" in your example.
Evan posted on 2/27/2014 4:57 PM
Great article!