Excel Tutorial – Example of how to use the VLOOKUP function

Posted on September 5, 2007 at 1:50 pm

Make sure to read other posts from the Excel Tutorials series!

Microsoft Excel Basics Tutorial – Learning how to use Excel
Excel Tips & Tricks – How to unprotect only a portion of an Excel spreadsheet
Excel Basics – How to use the Conditional Sum wizard
How to use Data Validation in Microsoft Excel to create dropdown lists
Excel Tutorial – How to make a simple graph or chart in Excel
Excel Tutorial – Example of how to use the VLOOKUP function

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!

[tags]vlookup, vlookup function, how to use vlookup, vlookup help, vlookup examples, vlookup tutorial[/tags]

» Filed Under MS Office Tips

Comments

15 Responses to “Excel Tutorial – Example of how to use the VLOOKUP function”

Rachel said on : February 23rd, 2008 at 2:54 am

Good for easy reading and understanding!

shila said on : April 17th, 2008 at 4:22 am

I have drop-down list of state example:
State1
State2
State3
State4
State5

State1 have Area 1, Area 2, Area 3
State2 have Area 1, Area 2, Area 3, Area4

if user select State1 in the state coloumn, Area coloumn must show drop-down list of area of State1 only.

Excell can do that?

SHASHIKANT said on : May 4th, 2008 at 3:08 am

Thank U very very very Much, your explanation really helped me to understand this very complicated functions.

I am not well conversant with the complete excel sheet function, I hope to continue to receive valuable inputs, tips to improve my knowledge in regards the EXCEL Spreadsheeet functions.
Thank you

c.arun kumar said on : May 9th, 2008 at 4:35 am

how to identify and express if a “$” symbol is present in any cell of the excell sheet? how will u name it?

MUTHUKUMAR N said on : May 10th, 2008 at 4:06 am

Its realy help to develop my excel knowledge.

Thanks & Regards
Muthukumar N

Wang Feng said on : September 17th, 2008 at 3:05 am

This example is very good!
the teaching way is very good-explaination with good example!

stas said on : October 2nd, 2008 at 1:37 pm

Hello.
I was reading your w. site and it is great. I like XL and working with it. I have one question regarding Vlookup formula. Could you please help me out with this one.
Let’s assume I have created some kind of list of clients with VLOOKUP function.
But sometime I need to delete client from the list or add one to the list. How should I change the formula in order to add client or delete from the list automatically? Other words , when I do not have a client in the list I type him up in the required sell , push Enter and add it to the list; so next time when I open drop-down menu it will be there.
Thank you very much in advance. Waiting for your reply.
Stas.

Jariya Diallo said on : October 23rd, 2008 at 11:22 am

Thank you very much. I tried to follow other examples. Thank goodness I found yours.

Dudley Cottingham said on : November 11th, 2008 at 9:52 am

Hi. I have no problems in using the VLOOKUP Function – so this may seem like a bizarre question – but I can’t quite get my head around the logic of the expressions TRUE and FALSE in the range_lookup argument.

To find an exact match I should enter FALSE (where TRUE would seem more logical to me)
To find the closest match I should enter TRUE (where FALSE would seem more logical to me)

Can someone please explain how Excel interprets these expressions?

Michael said on : December 15th, 2008 at 10:38 am

Need to know how to search column A of hundreds of numbers with numbers in Column C, to see if what I have in C is in column A. Using normal formula vlookup(A2,c2:d14,2) works fine only if I have column C numbers lined up exactly next to column A and in order (defeats my goal)In other words, I want to search existing records (column A)to see if a group of numbers are in there (column C) and prompt “Found”.

Iouri said on : January 5th, 2009 at 1:29 am

Microsoft should copy and paste this explanation to its Help. Many thanks. Iouri

David said on : March 19th, 2009 at 5:11 am

I have 2 excel sheets, first sheet contains customer details of day 1 and another sheet customer details of day 2, now i want a report using vlookup function of particular customer details from these 2 sheets

Plz guide me

Dawood Mamedoff said on : May 27th, 2009 at 8:33 am

Hi, thanks for explanation! For simpler illustration of how Vlookup works I’d also recommend the following video tutorial:

http://www.myhowtoos.com/en/ex.....ng-vlookup

Delmesha said on : August 3rd, 2009 at 10:45 am

Hello!

I’m trying to drag my vlookup formula down my worksheet in hopes of grabbing the details I need for each unique lookup, but when I drag the formula it copies the results of the initial cell. What’s interesting, is that the formula is changing relative to the corresponding cell but it keeps bringing back the same value. This is incorrect, because when I type in the formula manually, it returns the correct value! Can anyone help me?

Master Excel Tables with AutoFilter and Subtotal Says:

[...] pool. Several Online Tech Tips posts have already covered clever but unfamiliar functions like VLOOKUP and SUMIF. Now it’s time for another round of Outrageous Excel [...]
April 9th, 2008 at 5:23 am