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

Excel function tutorials

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel 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!

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

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


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

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

Deepthi

Thanks So much , very easy way to learn Vlookup



Brandon

Your Network Marketing will allow you be found online by a lot of internet users. Your site was created for the blog reader and their enjoyment.



Ashok Kumar Lenka

Very much useful.I understand immediately



SURESH

Simple and good to learn excel.

Thnku



Prabhakar

Excellent tutorial..easy to understand..great work..Thank you



Prakash

Very nice…..if we use vlookup from left to right cell means its working, Suppose we use from right to left means its not working. FOr ex:-
X
001 a
002 b
003 c
004 d

Y
c
a
d
b

above I have mentioned the list. i have to fetch data from X to Y. i want get data c,a,d & b… How to get it?
Please advice me for this……!!!



NIZAM

THIS IS EXCELLENT AND TOOO EASY….THANX A LOOOOT….NOW AM EXPERT IN THIS….



Steven

Very Smart, Easy and User Friendly
Thanku



vijay yadav

really its so good for me.



Amit

i m very happy to read this ….its very useful for me…i really ud like to thanks the team which has post this information of ms excel of V lookup…….thanks



MOHAMMED ASADULLAH KHAN

YES ITS FANTASTIC, IT MAKE MY WORK EASIER TO WORK.
THANX



Diane

Oh wow, this tutorial was just what I needed! I understand now and my formula worked! Thankyou Leslie!



Rajivranjan Kumar

I m very happy to read this formulla



Taban Stephen

So nice and easy to understand.When is part 11 coming up?



hina

It is very easy way of understanding that formula……..awesome….



Dharmesh

Thank you very much, Its very useful



SUNIL KUMAR

thanks a lot



VINAY

Good one.. Superb!!! Very useful



Gaitari

It was very helpful for me.

Lots of thanks.



http://duboisproed.org/elgg/blog/view/254999/enjoy-a-beautiful-new-you-with-this-advice

This is the perfect site for everyone who hopes to understand this topic.
You know so much its almost hard to argue with you (not that I
really would want to_HaHa). You certainly put a brand new spin on a subject which has been written about for a
long time. Great stuff, just great!



Rohan

Very well done bro, thanx for sharing as i nee it.



SHRAVAN

thanks SIBIYA KUDZANAI



Manoj

Thank for sharing it, But i also know the formula of Vlook up to Find Name ?



SANEESH

THANKS BRO..



noorain

thanks allot.learnt vlook up in just 5minutes.Am impressed.



yadagiri

Thank for sharing



Umesh Sharma

This is good learning site it is word less awesome ………………super



Ranjbar

million thanks to you and your society.
i was begging every one to show me but no one cared for me, god bliss you.



SURESH

Thank you, well explained



Anaclete

you are doing an awesome job my friend… thanks



Jailendra Kumar Poyam

excellent !..Great.. Thank you very much for sharing your expertise & knowledge in a simple way.



SUSHING CHENG

Thank you so much for this easy-understanding and clear way on explaining this! I could finally understand this!



Sunil bisht

How to use vlookup command in exel



Sri Kumar Pillai

Thanks a ton….nicely explained…



M.Mohsin Ranjha

bohat asan aur nafees tarika hy samjany ka bahi jaan



kartike

thanx.it is very simple idea to learn use of excel for beginer.



魔兽私服开区一条龙服务

传奇世界服务端刀剑服务端十二之天服务端新天骄服务端卧龙吟服务端
英雄王座travia私服开服暗黑修仙私服开服仙境传说ro私服开服弹弹堂私服开服
天堂I开区一条龙挑战OL开区一条龙剑侠情缘开区一条龙奇侠XIAH开区一条龙挑战开区一条龙
魔兽私服开区一条龙服务 http://www.n7ss.com/



anil

Nice ! still to practice , any more details
if provided shall be helpful to understand
easily

Great !!!



Pavan Chavhan

I really don’t know what abaut vlookup and hlookup but after viewing you’r example i got it. Really Youre explanation is awesome.



in chennai for wedding|wedding photographers in chennai|good wedding photographers chennai|professional wedding photographers in chennai|best wedding photographers chennai|professional photographers for wedding in chennai|chennai based wedding photographe

Aw, this was an extremely good post. Taking a few minutes and actual effort to create a great article… but what can I say… I procrastinate a lot and don’t manage to get anything done.|



エルバーキンコピーエルメスバーキン30コピーエルメス ボリード47,エルメス バッグ 名前,エルメス ネクタイ ピンク エルメス クラッチバッグ,エルメス バッグ コピー,エルメス バーキン コ

ブランドスーパーコピーバッグ、財布、靴、時計各種のブランドは表して、かばん複製品をコピーしますルイヴィトバッグ コピールイヴィトン財布 コピールイヴィトン長財布 コピーブランド偽物、偽物ブランド、ルイヴィトンコピー、 ロレックスコピー、シャネルコピー、グッチコピー、エルメスコピー、 ボッテガ?ヴェネタコピー、 バーバリーコピー、ミュウミュウコピー、トリーバーチコピー、バレンシアガコピー、ディオールコピー、ブルガリコピー、ブラダコピー、 ドルチェ&ガッバーナコピー、オメガコピー、フランク ミュラーコピー、gagaコピー。ルイヴィトン 時計 コピールイヴィトン 靴 コピールイヴィトン アクセサリー コピールイヴィトン 小物入れ コピールイヴィトン コインケース当店の全ての商品は、全部一番安い値段で販売いたします!ご来店を期待しております!
エルバーキンコピーエルメスバーキン30コピーエルメス ボリード47,エルメス バッグ 名前,エルメス ネクタイ ピンク エルメス クラッチバッグ,エルメス バッグ コピー,エルメス バーキン コピー エルメス 財布 ダミエ オークション,エルメス ヨーロッパ,エルメス エールライン エルメス クラッチ激安通販、高い品質、送料無料。バーキン25コピー、バーキン30コピー、バーキン35コピー、バーキン40コピーなど世界中有名なブランドレプリカを格安で通販しております。N級品スーパーコピーブランドは ブランドスーパーコピー超N品エルメスバッグ,エルメス バーキン25 , バーキン30.バーキン35.バーキン40. エルメス(HERMES) ケリー http://www.gowatchs.com/brand-258.html



スーパーコピーブランド格安販売店はこちらへ!品々の激安価格に持ったスーパーコピーブランド 代引きの新作はお客様に提供されます。安心、迅速、確実、お客様の手元にお届け致しま

ブランドスーパーコピーバッグ、財布、時計プラダ スーパーコピー,プラダ 財布 コピー,プラダ 新作 財布ブランド財布コピー,ブランド スーパーコピー 財布,プラダ スーパーコピー 財布,シャネル財布コピールイヴィトン 財布 コピー,ルイヴィトン 財布 コピー 代引き,ルイヴィトン財布スーパーコピー,ルイヴィトン 財布 スーパーコピー 代引き,ヴィトン財布代引き,ヴィトン財布 人気,ヴィトン財布 激安,モノグラム 財布,マルチカラー 財布,ルイヴィトン財布新作,ルイヴィトン バッグ 新作,ルイヴィトン スーパーコピー 即日発送ルイヴィトン スーパーコピー 専門店ルイヴィトン スーパーコピー バッグルイヴィトン スーパーコピー 財布ルイヴィトン スーパーコピー 長財布2015年新作 ルイヴィトン スーパーコピールイヴィトン コピー,ルイヴィトン コピー 財布,ルイヴィトン コピー バッグ
スーパーコピーブランド格安販売店はこちらへ!品々の激安価格に持ったスーパーコピーブランド 代引きの新作はお客様に提供されます。安心、迅速、確実、お客様の手元にお届け致します。★弊社は9年の豊富な経験と実績を持っております。★一流の素材を選択し、精巧な作り方でまるで本物のようなな製品を造ります。★品質を重視、納期も厳守、お客様第一主義を貫きは当社の方針です。★驚きの低価格で商品をお客様に提供致します!★早速に購入へようこそ! http://www.gginza.com/bag/chanel/index.html



deepak

bc,mc



raja p

Nice tutorial



RAJ KUMAR

thanks

VLOOKUP IS VERY SIMPLE AND USEFUL FORMOOLA………..



Comments

Please Leave a Comment!





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