How to use VLOOKUP in Excel – a simple tutorial (part I)

Excel function tutorials

If you use Excel much at your job, sooner or later, you’re bound to need to look up values in a table. One of the most useful functions in Excel, called , does exactly that. The “V” in vlookup stands for “vertical” and “lookup” is pretty self explanatory. This function allows you to look up values in a table that are listed in column format (how most tables are laid out), given another value (let’s call this the “key”). Excel also has a sister function called hlookup (h = horizontal) that can be used to look up values in rows.

Sadly, as most companies seem to rely on Excel as a poor-man’s database of sorts (a totally unscalable solution and prone to errors with every revision, but don’t get me started), once you know vlookup, it’s likely to become one of your most often used Excel functions.

So, let’s get started with a very simple example of what vlookup is all about. Suppose you had the following table:

Given a list of names in another part of the table (in this case, column H), you want to figure out what kind of animal it is:

Vlookup’s format looks like the following:

=vlookup(lookup value, table where values reside, column # where values are located, false)

Let’s look at each of these parts a bit closer.

The first thing that goes into the vlookup function is the thing you know (or are given) and that will be used to lookup other values. In this case, you have the names of the animals, so these are the things we know. In our example, they reside in column H, from cells H2 through H5. If we wanted to put the type of animal next to the name of the animal in column I (so I2 would correspond to the name of the animal in H2), we would insert the vlookup function there:

and put H2 as the first thing in our vlookup function:

Next, we need to know the location of the table where our values reside. These happen to be from cells A1 through B5 in this example, which we would highlight with our mouse to insert into the vlookup function. It’s very important that you include all the cells in the table.

Highlight the table with your mouse:

At the same time, the vlookup function automatically puts in the cells you’ve highlighted:

Next, we need the column number where the values are located. Always start with the first column (column A in this case) as #1 and count out to the right. In this example, the type of animal listed is in column 2, so that’s what we would need to insert in the vlookup function. Note that to use vlookup, your keys always have to be to the left of your values. (We’ll cover more of this in part II of the tutorial at a later date.)

Finally, the last attribute that vlookup takes is either “true” or “false”. I happen to always use “false”, and what this does is force vlookup to return the first exact value it finds. If that value isn’t found, then vlookup conks out and returns “#N/A”. Though we won’t use it in this example, if you select “true”, then rather than always looking for the exact value, vlookup will return the exact value if it exists, or the closest one to it that doesn’t exceed the key. (If you use “true”, you will need to sort your data in ascending order before using vlookup.)

Still with me? Again, this is what we would actually put in cells I2 if the names of the animals we have are located in cells H2 through H5:

=vlookup(H2, A1:B5, 2, false)

Once we close off the parenthesis and hit “Enter”, vlookup automatically calculates:

And so on. We would continue down each cell in column I that we needed. One thing to note is to make sure that the location of your keys and values is always selected correctly. Oftentimes, as you copy-and-paste formulas all around Excel, the location of the data will also move around relative to the cell. The easiest way to prevent this is to “lock” the range of the location; in this case, we would do so by using “\$A\$1:\$B\$5” instead of “A1:B5”. This way, as we move down column I, say, to cell I2, A1:B5 doesn’t become A2:B6 but stays with the original range of data. This way, we can just copy what’s in cell I2 down the rest of the cells (from I3 through I5):

Finally, here’s our result, after making the “\$” changes and copying and pasting the formula down the rest of the column:

This has been a really simple example of vlookup, and I’ll cover a bit more in part II with another example, still simple, but with slightly more data.

Although in practice, vlookup is usually used between Excel sheets and workbooks, once you understand this example (which has been done within a single sheet), using vlookup outside the same sheet shouldn’t be much harder. Look for part II soon!

***************************************************

***************************************************

473 Feedbacks on "How to use VLOOKUP in Excel – a simple tutorial (part I)"

seema Ray

Thanxs a lot,its v easy to understand within a short time.

Harshitha R

very simple, I used to break my head for this

Jayshil Patel

simplified and nicely explained…thanks

Harshitha R

I am not getting that subscription link even after filling the form so many times

Stuart Humphries

I haven’t used VLOOKUP for some time.
With this guide, I was back in the groove within minutes.

charanjeet

What is the use of v lookup function and other function.

Santosh Sahu

thanks.. a lot thank u for providing this information it’s very useful

good guidence

very exciting

pranab chakravarty

Very well explained.

pranab chakravarty

very well explained

Mario Buyayo

Sir/Madam:

your step by step tutorial on how to use vlookup is exciting. I would like to do this kind of work. In preparing reports, what I want to do is to just want to type the given ID # of a person or member and the name and other personal information of the member will appear based from an excel file. Any assistance you will give me on this regard is highly appreciated. Thank you and God bless.

Milena

Nicely explained! Thank you so much.

Pawan Kalyan

Great Info….Where is part 2

THARUN

I DID AS YOU SAID BUT I RECEIVED NA

THARUN

KINDLY SEND THE DETAILS TO MY EMAIL ID…

Ajit Kumar

Sir i can’t apply the formula for matching the from one sheet to another..

Srishti Singh

Easy to understand.Very simple to understand.

Aditya

It is easy to understand………..unique

Rakesh Kumar

Its really amazing blog with very much helpful information, thank you so much for writing this great blog here for us.

Lonappan, Edmund P.

Complex VLookup, explained in an understandable manner!

gorishanakar

thanks for this formulla it is usefull for official work .
by – gorishankar kumawat 7733890536

DEVENDRA

it is very easy to understand,i was unusually putting lot of pressure to understand,thankyou

Comments

Please Leave a Comment!

Please note: Comments may be moderated. It may take a while for them to show on the page.