How to do a VLookup in Excel
While I’m sure there’s plenty of other tutorials out there, I’m asked this question quite often, and wanted to add my spin (including additional tips/tricks) on how to do a VLookup in Microsoft Excel.
What is a VLookup?
Microsoft’s definition (from Excel 2016)
Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify…
My translation
A VLookup can be used to combine two separate data sets together. For example, if you have a list of Employee ID & Name and different list of Employee ID & Location, you could create a combined list of Employee ID & Name & Location.
The “V” in VLookup stands for “vertical” — that’s how we’re looking up the data. There is also a formula for HLookup (horizontal).
The Setup
Your two data sets can be in the same worksheet, same workbook (on different tabs), or two different files. Personally I tend to put everything in one file, but on different sheets.
In the place where you’re looking data up from, make sure the column you’re going to match is in the first (left-most) column.
The Formula
The formula is:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
It’s the formula name (“VLOOKUP”) followed by 4 parts in parenthesis. Each part in the parenthesis is called parameter.
Explanation of the 4 parts in the parenthesis (the parameters):
- lookup_value = what are you looking up?
- table_array = where are you looking it up?
- col_index_num = in the place you’re looking it up, what column number is it in?
- [range_lookup]= always put in “false.” The brackets mean that it’s an optional parameter (you don’t even have to specify it). If you put “true” then Excel will find an approximate match. I’ve always wanted an exact match, so I put in “false”. I’ve never put in “true” for this value.
Seeing it in Action
I’m going to put the the two data sets in the same Excel worksheet because it will be easier to view in this tutorial; however the principle is the same had the data sets be in different worksheets (or even different workbooks).
Suppose I have an Employee ID and Employee Name (Columns A & B). Separately, I have data for Employee ID and Location (Columns F & G). Since there is a common data element that’s the same in the two groups (Employee ID), I can bring data from one set into the other. Let’s put the Location into Column C (from Column G).
First, make sure that in the source of your data (here, Columns F & G), has the data element you’re looking up (Employee ID) in the leftmost column (which, it is — there are two columns: F & G. The one we’ll be “looking up” is the first of these two columns).
In the cell you want to put data in (here, C2), type in the function: =VLOOKUP(
and then you can specify the parameters.
Tip: as soon as you type in =V, hit the tab key on your keyboard and Excel will auto-complete the rest of the function name.
The first parameter (“lookup_value”) means “what are you looking up?” Here, we want to look up the associated Employee ID for the row that we’re on. You can type in the value (ex. “A2”), or use your mouse to click into the Cell A2 to select it.
After you specify the cell, type the comma character (“,”).
The second parameter (“table_array”) means “where are you looking it up?”. In this example, we are looking up data from the Columns F & G. For this example, it’s only two columns; but you could potentially have several.
We really only need the range of F2 to G7. I find it easiest to use the mouse to specify the cells (rather than typing it in).
Tip: in many cases, you can just specify the entire column. This avoids you needing to scroll down to the bottom if there are a lot of rows. There’s an additional benefit to this, covered in the “Advanced” example later in the tutorial.
After that, put in a comma so you can specify the next parameter.
The third parameter is for specifying the value (column) that you want. In this example, there are only two columns (F & G). The value we want (Location) is in the second column. So we just put a “2” as the value for this parameter.
There’s one last parameter. Put in a comma after the column number, and you’ll see there are two possible values we can use: true or false.
In my experience, I always want an exact match, so I always specify false.
Almost done! To end the function, put a closing parenthesis (which matches the opening parenthesis right after the word VLOOKUP in the formula). Then hit Enter.
The final formula looks like this:
You can see from the example above that it did find the Location for the specified Employee ID!
Copy (or drag) the formula downward, and you can see that the Locations fill in, where available.
Note that if a value isn’t found, then #N/A will show up (in the above example, rows 5 and 7).
Also note that it doesn’t matter if there’s extra data in the spot where you’re looking up (here, Cells F7 /G7 and even F8 / G8 which are blank). There was no request for it in where we’re looking it up from (Column A), so it didn’t break anything just by being there.
But Wait, There’s More…
Lock the Range
If you copy this formula (say, to the row right below it; or even in some cases if you needed to copy next to it), then the table_array (second parameter) will change. For the previous example, it doesn’t matter because we specified the entire Column.
But if we did this:
Then when we copy the formula down, we get this:
See how the rows in the formula changed? As you keep copying the rows down, the formula keeps changing. We don’t want this. We want to always look up the data between F2 to G7.
We overcame this by selecting the entire column. But the other way is to “lock” those cells. You can do this by putting a dollar sign right before the letter and/or number of the cells (Windows keyboard shortcut is to highlight the cells in the formula, and press F4).
So the trick is to make the formula look like this instead:
Now when you copy the formula downward, the range we are looking up (F2 to G7) won’t change. The value you are looking up (Cell A2) will change, but we want this.
Just when you thought you were done…
The data in Column C is a formula, and it relies on Columns F & G. If you were to send this to someone and they accidentally deleted Columns F & G, the contents of the formula would be gone. Or, what if the formula referenced a completely different file and the person you sent it to didn’t have it?
Tip: upon completion, Copy the data, and then do a Paste Special >> Values over it. Now it’s no longer a formula. If the other data isn’t even needed, you can delete it (Columns F & G).
If the value isn’t found, then #N/A will display. I typically do a Find/Replace (keyboard shortcut Ctrl + H) and replace it with a blank value.
One Last Tip: Use a Named Range
Rather than specify a range of where you’re looking up the data from (ex. “Columns A to M”), you can give that range a name. First highlight the range of where you’ll look up the data:
Then at the top left, next to the formula bar, there’s a box that says what cell you’re in. You can change that to any value you’d like — for example, “data”.
Now when you do the VLOOKUP, just specify the custom name of the range (here, “data”) instead of the Columns:
You can view and modify the Named Ranges in Excel in the “Name Manager” — Windows keyboard Shortcut is Ctrl + F3
A VLookup best practice
When possible, use a Unique Identifier to look up values. For example, looking up a value by last name may not be accurate if there are two people with the name “Smith”. Employee ID would be a better choice.
My most common VLookup data issue…
Sometimes when you do a VLookup, and you’re sure that you did the formula correct, but it doesn’t give the right results- you think you’re stuck. Quite often I find that there’s an extra blank space at the beginning or the end of the text. Because we’re looking for an exact match, the desired results won’t come up. The solution: to use the TRIM() function to remove any extra spaces.
Do this in both the “what you’re looking up” and the “where you’re looking it up” values. After I TRIM the values, I typically do a Copy/Paste Special >> Values over the original data.
A lesser common data issue is that sometimes I encounter what looks like a space, but isn’t. So TRIMming the data doesn’t work. It’s some sort of other character. In this case, I’m able to copy the single fake “space” character, and do a Find >> Replace on it (where, I replace that character with nothing to remove it).
Speaking of Data Validation…
I recently helped someone with a VLookup; everything worked, but the final output was not what they expected. It turns out that the data had multiple values for each data element they were looking up.
Take a look at this example:
Here I was looking up the Course Cost (Columns D & E). The person I was helping was expecting to see Course ID #1 have a Cost of $20 (Cell E3). But instead it had a value of $10, from Cell E2.
If you have duplicative data, the VLookup will return the first result it finds.
An advanced example
Sometimes I’ll need to bring in multiple fields from a separate data source. Here’s a technique that may help you save time.
Let’s say that we needed to bring in Columns J / K / L / M into Columns C / D / E/ F:
There’s a shortcut to dynamically specifying the “column number”. While this example is only with 4 columns, imagine if you had many more!
Temporarily create a new Row 1. Over the columns that you want to bring in, specify the respective column number.
Now in the formula, instead of hard-coding a number, reference the cell that it’s in. Here, I’m saying to use the column number (value) from Cell J1.
As you copy over the formula to the next column to the right, the value will increment automatically. Be sure to “lock” the value that you’re looking up, as well as the range of cells where you’re looking it up though!
When you’re done, Copy / Paste Special >> Values, and you can remove the first row that you had temporarily put in.
One last tip
You need to know the column number of which data element you want to bring in. If your table_array (where you’re looking up the values) has a lot of columns, you’ll need to count them manually. If your data starts in Column A, and you go out to Column M, you may not remember that you went 13 columns.
But if you look closely where the mouse is (while you’re still holding the button down), there’s a little pop-up that says how many rows and columns you’ve moved.
In this example, I’ve gone 13 columns; so that’s the number to specify in the formula.
