How to Use VLOOKUP in Excel

Here’s a quick tutorial for those who need help using the VLOOKUP function in Excel. VLOOKUP is a very useful function for easily searching through one or more columns in large worksheets to find related data. You can use HLOOKUP to do the same thing for one or more rows of data. Basically when using VLOOKUP, you’re asking “Here’s a value, find that value in this other set of data, and then return to me the value of another column in that same set of data.”

So you might ask how this can be useful? Well, take for example, the following sample spreadsheet I have created for this tutorial. The spreadsheet is very simple: one sheet has information on a couple of car owners such as name, id of the car, color and horsepower. The second sheet has the id of the cars and their actual model names. The common data item between the two sheets is the car id.

Now if I wanted to display the name of the car on sheet 1, I can use VLOOKUP to lookup each value in the car owners sheet, find that value in the second sheet, and then return the second column (the car model) as my desired value. So how do you go about this? Well first you’ll need to enter the formula into cell H4. Notice that I have already entered the full formula into cell F4 through F9. We’ll walk through what each parameter in that formula actually means.

Here’s what the formula looks like complete:

=VLOOKUP(B4,Sheet2!$A$2:$B$5,2,FALSE)

There are 5 parts to this function:

1. =VLOOKUP – The = denotes that this cell will contain a function and in our case that is the VLOOKUP function to search through one or more columns of data.

2. B4 – The first argument for the function. This is the actual search term that we want look for. The search word or value is whatever is entered into cell B4.

3. Sheet2!$A$2:$B$5 – The range of cells on Sheet2 that we want to search through to find our search value in B4. Since the range resides on Sheet2, we need to precede the range with the name of the sheet followed by an !. If the data is on the same sheet, there is no need for the prefix. You can also use named ranges here if you like.

4. 2 – This number specifies the column in the defined range that you want to return the value for. So in our example, on Sheet2, we want to return the value of Column B or the car name, once a match is found in Column A. Note, however, that the column’s position in the Excel worksheet does not matter. So if you move the data in Columns A and B to D and E, let’s say, as long as you defined your range in argument 3 as $D$2:$E$5, the column number to return would still be 2. It’s the relative position rather than the absolute column number.

5. False – False means that Excel will only return a value for an exact match. If you set it to True, Excel will look for the closest match. If it’s set to False and Excel cannot find an exact match, it will return #N/A.

Hopefully, you can now see how this function can be of use, especially if you have lots of data exported out from a normalized database. There may be a main record that has values stored in lookup or reference sheets. You can pull in other data by “joining” the data using VLOOKUP.

Another thing you may have noticed is the use of the $ symbol in front of the column letter and row number. The $ symbol tells Excel that when the formula is dragged down to other cells, that the reference should remain the same. For example, if you were to copy the formula in cell F4 to H4, remove the $ symbols and then drag the formula down to H9, you’ll notice that the last 4 values become #N/A.

The reason for this is because when you drag the formula down, the range changes according to the value for that cell. So as you can see in the picture above, the lookup range for cell H7 is Sheet2!A5:B8. It simply kept adding 1 to the row numbers. To keep that range fixed, you need to add the $ symbol before the column letter and row number.

One note: if you’re going to set the last argument to True, you need to make sure that the data in your lookup range (the second sheet in our example) is sorted in ascending order otherwise it will not work! Any questions, post a comment. Enjoy!