VLOOKUP function – Excel Formula

655
Vlookup Tutorial
Vlookup Tutorial

Vlookup function

This is the best formula among all excel formulas. Hence you are going to learn this formula in detailed in this article. Also you are going to know all the vlookup advantages and disadvantages ( Limitations ), definition,Syntax, Approximate Match, Exactmatch, which areas can be used the vlookup, Vlookup in same worksheet, Vlookup with duplicates, Vlookup different sheet, Named range in Vlookup, How to create Named range in excel and Vlookup Meaning.Vlookup full form is vertical lookup, Unless we follow the syntax, we get the result as vlookup Errors 

Vlookup definition:


Using this function we can get the right side values based on left values. In the below example 1 you can find the same.

Syntax :

Note: Always it is better to sort the values of the data in ascending or descending depending on your requirement to get the results else there might be chance to get unexpected results by this formula.

Example 1:

Vlookup in same worksheet:

Vlookup in Same worksheet Example
Vlookup in Same worksheet

Vlookup Syntax Explanation:

Lookup Value:  G4 cell is the Lookup value in the below example

Table Array:  C column and D columns are Table array

Column index Number: Since we select only two columns C and D. And we require the 2nd column values as results, we are going to give the column index number as 2

Exact match: 99.99% of time we regularly use the exact-match that is denoted by zero ‘0’. Here in the below example you can find the exact-match only.

Example 2:

Vlookup from another sheet:

It is also same as example 1, only one difference is that you need to add the sheet name to the table array in the below syntax. Here in the below example you can find the data is in the vlookup data sheet. And result in the Vlookup result sheet. Hence the syntax would be as shown in the below picture.

Table Array:

It has the change that is ‘Vlookup Data’!

vlookup from another sheet Data
vlookup from another sheet-Data

Vlookup from different Sheet as shown in the below

vlookup from another sheet-formula
vlookup from another sheet-formula

Approximate match: Very rarely we use this approximate-match that is denoted by ‘1’

Example 3:

Vlookup Example To Use Approximate Match
Vlookup Example To Use Approximate Match

The below areas can be used the vlookup function :

Userforms, subroutines, modules, textbox, listbox, combobox, worksheets (with in the same worksheets,), different excel worksheets (means one sheet to another worksheet with in the same workbook), different excel workbooks  and User defined functions

Vlookup advantages:

  1. Mainly this function can be used for the purpose of searching values for the respective lookup values.
  2. VLOOKUP is simple formula and very powerful in excel spreadsheet
  3. Many Excel formula can be used within the and along with VLOOKUP
  4. Assigning the right values for the given lookup values as I have explained you in the approximate match example in the above.
  5. We can customize the function by taking the support of the other excel functions like countif, sumif, count, countifs, sumifs, and sum function. This function can be used with the combination of the sumproduct, if function, nested if function, Indirect, index and match functions.
  6. VLOOKUP can be customized in the user defined functions as and when we require. This is one of the major advantages.
  7. This function can be used for Pivot tables and tables also to get the exact values
  8. We can use the named ranges for range criteria in the vlookup syntax as shown in the below

Named Range in the Vlookup:

Named range is nothing but a collections of cells within the range where you can provide the name for that specific range

Example:

Vlookup Example to use Named Range
Vlookup Example to use Named Range

Named Range In Excel:

Creating Named Range in Excel Spreadsheet
Creating Named Range in Excel Spreadsheet

Vlookup disadvantages ( Limitations ):

  1. Vlookup can not get the right value when there are duplicates in the lookup value related column. We must need to sort the data when there are duplicate values in the lookup value related column. Then this function can return the first value related value This is the major drawback of this function. Of course we can overcome this problem by using the different functions in the excel workbooks

Vlookup with duplicate values:

Example 4:

Vlookup without sorting for duplicate lookupvalue
Vlookup without sorting for duplicate lookupvalue

 

2. Using this function we can only pull the right side values as we discussed in the above. If we need to pull the left side values based on the right side value. We can use the reverse vlookup function. This is the combination of the index and match functions.

3.It only works on columns if we need to use for the rows then we have to use the Hlookup function which is very similar to Vlookup function. Hence reverse vlookup which is the combination of the index and match functions.

4. Vlookup major disadvantage is when you have more vlookup formulas within Spreadsheet or within one Excel workbook, it hangs a lot since it is an array formula. When user getting hanged in the workbook that time user need to setup formulas calculation option as manual, you can find the below screen shot to setup manual option in the excel.

Example 5:

Formula Calculation option setting as Manual
Formula Calculation option setting as Manual

Once we finish working in the excel, we need to set back as Automatic in the above option as usual

Vlookup Meaning:

Vlookup significance is nothing but a Vertical lookup where you can search the data vertically based on the lookup values availability. As I have explained you in the above examples. Vlookup is one of the key functions within the excel Spreadsheet. Almost many MIS tasks get complete with only with this function. Still this has some limitations as we have already discussed in the above. But we can overcome this lose by taking the support of the other excel functions. Here in the excel Vlookup point of view vertical in the sense column by column

Example 6:

Column A, Column B wise from left to right we can search the values using this VLOOKUP formula in Excel spreadsheets. Please observe the below Screenshot for better understanding

Vlookup searches from Left to right vertically column wise
Vlookup searches from Left to right vertically column wise

There are many wonderful examples can be derived by Vlookup in the live projects, those also we are trying to include in this article at the earliest.

This entire article can be considered as Vlookup tutorial

See Also :

Index & Match Combination ( Reverse Vlookup)
VLOOKUP Error types 
How To Use If And VLOOKUP Together

655 COMMENTS

  1. It is nice example and everything covered. I want to learn the VLOOKUP along with other formula. Is it possible to do the more examples on Vlookup with other formula combinations. However this you have explained in simple way to understand. Now I know the Vlookukp very well. Even VLOOKUP Syntax I can write easily

  2. This is an excellent tutorial and explanation of the benefits of VLOOKUP. Knowing how to use Excel is crucial for success in business and these practical, easy to understand examples are beneficial to any new or beginner user. I look forward to reviewing more materials on this website! Well done!

    • Thanks AJ,

      I am going to provide all the excel funcations and VBA code tricks over here at the earliest. Hence please subscribe.

  3. Just checked through quickly but I see it’s a very good stop point for Excel lovers like me, like u, like any other…
    Thanks for the great work.
    Thanks more

    • I will add more stuff regards to VLOOKUP. Hence Keep watching. You can see the Vlookup with other functions.

      VLOOKUP vs Other functions. Stay tuned….

  4. I have checked your website and it is so nice about the VLOOKUP function. I like this very much and i will share this in my timeline. Thank you very much for providing VLOOKUP Tutorial

    • Thank you very much for your appreciation and I am going to include all about the VLOOKUP formula at the earliest. And also advise me in case if i have to include any thing in this. I have covered 100% about the VLOOKUP. Still I will cover many examples and advantages of the VLOOKUP function in Excel spreadsheet

  5. Hi, just wanted to let you know that the you have given wonderful information regarding VLOOKUP.
    Thank you very much for this

    • Thank you for read the article. Also please let us know if you still need to more example on VLOOKUP. And we will take some time and provide more example

    • Thanks for your feedback. I am going to do more examples on VLOOKUP in future. Please keep watching the tutorials also provide ideas to implement this article

  6. I seriously love your blog.. Very nice colors & theme. Did
    you build this site yourself? Please reply back as I’m looking to create my own personal
    website and would love to find out where you got this from or just what
    the theme is named. Thank you!

  7. I have noticed you don’t monetize your site, don’t waste your traffic, you can earn extra bucks
    every month because you’ve got high quality content.
    If you want to know how to make extra $$$, search for: Ercannou’s essential adsense
    alternative

  8. I have checked your website and i’ve found some duplicate content,
    that’s why you don’t rank high in google’s search results, but there is a tool that can help you to create 100% unique content, search for:
    SSundee advices unlimited content for your blog

  9. I often visit your page and have noticed that you don’t update it often. More
    frequent updates will give your website higher rank & authority in google.
    I know that writing content takes a lot of time, but you can always help yourself with miftolo’s tools which will shorten the
    time of creating an article to a couple of seconds.

  10. I often visit your blog and have noticed that you don’t update it
    often. More frequent updates will give your
    blog higher authority & rank in google. I know that writing
    posts takes a lot of time, but you can always help yourself with miftolo’s tools
    which will shorten the time of creating an article to a few seconds.

  11. definitely know how to keep a reader entertained. Between your wit and your videos, I was almost moved to start my own blog (well, almost…HaHa!) Fantastic job. I really loved what you had to say, and more than that, how you presented it. Too cool!

  12. I often visit your page and have noticed that you don’t update it often.
    More frequent updates will give your blog higher rank &
    authority in google. I know that writing content takes a lot
    of time, but you can always help yourself with miftolo’s tools
    which will shorten the time of creating an article to a
    few seconds.

  13. This is really interesting, You are a very professional blogger. I’ve joined your feed and sit up for seeking extra of your wonderful post. Additionally, I’ve shared your website in my social networks

  14. В настоящее время покупатели проявили большой интерес к новинке – живым цветам в стекле. Они прекрасно смотрятся на столе, на тумбочке, по внешнему виду ничем не отличаясь от обычных живых цветов, подробнее читайте на сайте http://tvoi-noski.ru

  15. Ревность является отрицательным и разрушительным чувством для любовных отношений. Конечно, в малых дозах она даже полезна, но здесь речь идет о том, что человек психически нездоров. Читайте об этом подробнее на сайте ladysarafan.ru

  16. Для контроля работ на строительном объекте вводится строительно-техническаяэкспертиза, которая подразделяется на несколько видов. Так, экспертизе подвергаются: здания и сооружения, работы, строительные проекты, качество строительства, недвижимость, судебная экспертиза строительства, подробнее читайте на сайте http://montazhnik02.ru

  17. I can see that your content probably doesn’t have much traffic.
    Your posts are awesome, you only need more new visitors.

  18. Hi. I see that you don’t update your site too often. I know that writing posts is
    time consuming and boring. But did you know that there is a tool that allows you to create new articles using existing content (from article directories or other websites from your niche)?
    And it does it very well. The new articles are high quality and pass the copyscape test.
    You should try miftolo’s tools

    • I don’t like to use tools to create articles. Whenever I like, i will write one article for one concept in excel like this vlookup function. However thanks for you advice

  19. I have never read earlier about this kind of excellent article so far. Thanks a lot for providing to learn about vlookup

  20. whoah this blog is magnificent i really like studying your articles. Stay up the great work! You know, lots of individuals are looking round for this info, you could aid them greatly.

  21. These are genuinely fantastic ideas in regarding blogging.
    You have touched some fastidious points here. Any way keep up wrinting.

  22. I’ve been surfing online more than 3 hours today, but I never discovered any interesting article like yours. It is pretty value enough for me. In my view, if all site owners and bloggers made excellent content material as you did, the web might be a lot more helpful than ever before.

  23. Aw, this was a reаlly nice post. Spending some time and actual effort
    to generate a very good artiϲle… but what can I
    saʏ… I procrastinate a lot and never seem
    to get anything done.

    • Vlookup is very simple and powerful one. And we have to give the information as much as possible that’s why i am procrastinating a lot.

  24. It is wonderful article about Vlookup. Thanks for providing this and do more articles like this which would help a lot to people

  25. It is highlight post about vlookup. Thank you very much for giving useful information about vlookup and other formulas. Your blog is nice

  26. Every weekend i used to go to see this web site, because i wish for enjoyment, as
    this this web page conations in fact nice funny stuff
    too. Vlookup formula related post is very useful.

  27. I’d like to thank you for the efforts you have put in writing this site.
    I am hoping to see the same high-grade blog posts by you later on as well.
    In truth, your creative writing abilities has inspired me to get my
    very own blog now 😉

  28. I don’t even know the way I finished up right here, however I thought
    this post used to be great. I don’t know who you are but certainly you’re going to a well-known blogger if you
    aren’t already. Cheers!

  29. With havin so much content and articles do
    you ever run into any issues of plagorism or copyright violation? My
    website has a lot of exclusive content I’ve
    either written myself or outsourced but it looks like a lot of it is
    popping it up all over the internet without my authorization. Do you know any solutions to help prevent content from being
    ripped off? I’d certainly appreciate it.

    • Since I am writing by my own, I have never faced such problem so far.If it is own blog better to write by own or hire the trusted guy by paying good amount. Then you won’t get such problem.

  30. What’s Happening i am new to this, I stumbled upon this I’ve found It absolutely helpful and it has aided me out loads. I hope to contribute & aid other users like its helped me. Good job.

  31. It’s the best time to make some plans for the future and it is time to be happy.
    I have read this post and if I could I wish to suggest you some
    interesting things or advice. Perhaps you could write next articles referring to
    this article. I wish to read more things about it!

  32. Usually I do not read post on blogs, but I would like to say that this write-up very compelled me to check out and do so! Your writing taste has been surprised me. Thank you, quite nice post.

  33. Write more, thats all I have to say. Literally, it seems as though you relied on the video to make your
    point. You obviously know what youre talking about, why throw
    away your intelligence on just posting videos to your site when you could be giving us something enlightening to read?

  34. I’m working on a new list. I’m hopeful that this one will be much bigger.
    I made some announcements about my future site plans.
    I’m going to be adding some new stuff soon. You’ll definitely want to stay tuned for that.
    Thanks for your time and have a good weekend!

  35. Why our software is the World’s most powerful link building software ?

    Our Software supports UNLIMITED website platforms.
    You can submit your backlinks/content to following main platforms with support built right in:
    • Web 2.0 Blogs
    • Social Network Posts
    • Social Bookmarking
    • Web Directories
    • Wiki Articles
    • Press Release
    • Article Directories
    • Web 2.0 Profiles
    • Forum Profiles
    • RSS

    [GET FREE TRIAL] ==> http://www.moneyrobotsubmiter.club

  36. I’m working on a new list. I’m hopeful that this one will be much
    bigger. I made some announcements about my future
    site plans. I’m going to be adding some new stuff soon. You’ll definitely want
    to stay tuned for that. Thanks for your time and have a good
    weekend!

  37. Nice weblog here! Also your web site a lot up fast! What host are you the usage of?
    Can I get your associate hyperlink to your host?

    I want my web site loaded up as fast as yours lol

  38. Terrific work! That is the kind of information that are meant to be shared around the internet.
    Disgrace on the search engines for not positioning this submit upper!
    Come on over and seek advice from my web site . Thanks =)

  39. It is very nice to study and learn. please write more articles to read. These are really helping us to improve our knowledge on excel formulas

  40. Finally I have learnt about vlookup formula completely from your article. Please write the article about Index and Match formula combination

  41. Just beneath, are a lot of totally not associated internet sites to ours, on the other hand, they’re surely really worth going over.

  42. Attractive element of content. I just stumbled upon your website and in accession capital to say that I get in fact enjoyed account your
    weblog posts. Any way I will be subscribing to your augment or even I achievement you get right of entry to persistently fast.

  43. When I originally commented I clicked the “Notify me when new comments are added” checkbox and now each time a comment is added I get several emails with the same comment.
    Is there any way you can remove me from that service? Thanks a lot!

  44. Hello there, just became aware of yߋur blog through Google,
    and found that it’s really informаtive. I am going to ᴡatch out fоr brussels.
    I’ll appreciate if you continue this in future. A lot of peoplе will be
    benefited fгom your writing. Cheers!

  45. This weblog is excellent, i like reading your posts. Stay up the good work!!!
    You already know, lots of individuals are searching
    round for this info, you could help them greatly.

    • Yes, You are right. I am setting up this blog is to help people who wants to learn or know about excel formulas and VBA programming

  46. I’m really enjⲟying the design and layout of your blog.
    It’s a very easy on the eyeѕ ѡhich makes it much more enjoyable for me
    to come here and visіt more often. Did you hіre out a deveⅼoper to create your theme?
    Great work!

    • HI, Thanks for enjoying by visiting my blog. And I am a developer, hence i am only building this website slowly by my own

  47. Very couple of internet websites that happen to be detailed below, from our point of view are undoubtedly nicely worth checking out.

  48. fantastic post, very informative. I ponder why the other specialists of this sector do not realize this.
    You must continue your writing. I’m sure, you’ve a huge readers’ base already!

  49. We prefer to honor a lot of other world wide web web-sites on the internet, even when they arent linked to us, by linking to them. Below are some webpages really worth checking out.

  50. Every weekend i used to go to see this web page, as i wish for enjoyment, since this this web page contains actually pleasant funny material too.

  51. I used to be recommended this web site via my cousin. I am no longer sure
    whether this put up is written by him as no one else understand such specified about my difficulty.
    You are amazing! Thanks!

  52. I thіnk this is one of the most important info for me.
    And iam glad reading your aгticle. But want to
    remark on some general things, The weƄsite
    style is great, the articles is really great : D. Good job, cheers

  53. I Ԁon’t even know how I ended up hеre, but I thought tһis post was good.
    I don’t know who you aгe but definitely you are going to a famous blogger if you aren’t аalready 😉 Cheers!

  54. Beⅽause the admin of this website iѕ working, no question very quickly it wіll become famous website, due to its
    quality contents.

  55. Every once in a while we choose blogs that we read. But your blog we read frequently in order to know more news about excel

  56. It is really very cool website to learn Excel Formulas and VBA. Please write more articles and do seo from us. However you are writing nice articles for visitors. It is really helping us to learn excel from you blog. Very soon it is going to get popular

  57. I have noticed that you have written nice posts , write more articles in same manner
    you can get best rank in global alexa. Keep improving this blog to get more and more traffic. wish you all the best

  58. Hello. I see that you update your blog too often. I
    know that writing articles is tough job and time consuming.
    But you still manage to write more articles. You are simply nice to work hard and smart. Keep rocking to awesome rank

  59. We just came to blog and visited. I was just shocked by looking your articles. A lot of secrets about excel formulas were revealed. Thanks for that. I frequently read your articles to learn more better

  60. Your Vlookup tutorial helped me a lot to learn Vlookup formula. Thank you. I will wait to see more new articles from you. Thank you

  61. Hi there excel-formula.com

    Excel Formulas relating articles process requires a lot of time.
    If you have excellent knowledge then you will know the amount of work load involved in creating Excel formula related articles, confirming emails and conveying your contents to thousands of people in a proper time to make them understand and learn excel formulas completely.
    You are the best person to teach excel formulas to visitors in a simple manner, you will be able to write unlimited number of articles and increase traffic to your website which will lead to a higher number of Excel learners and much more requests to write few more articles like this.
    with your good expertise knowledge it is very easy to convey people to learn excel here in your blog without any pressure.
    The best blog for Excel formulas, and we can confidently say that there is no other website on the market that can compete with such intelligent and well explained articles.
    You are providing user friendly articles to learn excel formulas to visitors

    Thank you

  62. I am watching this website from many days, I am learning Excel from this blog. It is very useful to learn quickly excel formulas. Thank you

  63. I am very interested to ready your article since you are providing valuable information to learn excel formulas in easy manner. I became big fan of your blog.

  64. I like the helpful information you provide in your articles.
    I’ll bookmark your weblog and check again here regularly.
    I’m quite certain I’ll learn plenty of new stuff right here!
    Good luck for the next!

  65. We appreciate you other sorts of outstanding publish about Vlookup. The location different may perhaps any individual obtain that form of info in their normal perfect means of publishing? But I appreciate you for this smart work to teach excel formulas to visitors easily.

  66. Hello. I see that you are updating your blog too often. I know that
    writing articles is time consuming and boring. But how are managing to write content continuously
    from your niche)? And it does it very well. The new articles
    are unique and pass the test

  67. I got this web site from my buddy who informed me on the topic of this web site and now
    this time I am visiting this web page and reading very informative articles
    at this place.

  68. If you are a Vlookup lover for https://excel-formula.com/vlookup-function-excel-formula/ you can learn very easily. This formula automatically teach you many things in excel to work easily play with data. And the quality of the Excel reports also can be found https://excel-formula.com/category/mis/ Mis reports via your computer or mobile. You can learn many reports https://excel-formula.com/how-to-create-excel-dashboard/ the absolute best sites for learning Excel reports via online. Free tutorials are required by https://excel-formula.com/how-to-create-excel-dashboard/ visitors, start off on a learning excel formulas using this blog. Note at Excel formulas learning today! If you are looking to learn excel better way https://excel-formula.com/ can help you. And you can find articles for every excel formula on this site, and try it out. Join at https://forum.excel-formula.com blog online, we give you roulette tips and strategies. For information on excel formulas at https://excel-formulas.com. All excel formulas are available in thousands right now at https://excel-formula.com/ in our updated list today. When you want to learn online at Excel subject you https://excel-formula.com is the most popular blog of gaming for many casino players. Casino night roulette sweden online https://22ef87a000.weebly.com/blog/casino-online with bst live casino bonus online. On this website we will help you to teach all excel shortcuts, excel formulas, VBA macros, Excel Macro programming to you. you just provide reviews as comments in the articles below.

    • Thanks for your article on my blog. I am really very happy with your blog post in my blog comment section. It helps visitors to know about my blog.

  69. I know this if off topic but I’m looking into starting my own blog and was curious what all is required to get setup?
    I’m assuming having a blog like yours would cost a pretty penny?
    I’m not very internet savvy so I’m not 100% certain. Any recommendations or advice would be greatly appreciated.

    Appreciate it

  70. Hi there! I understand this is somewhat off-topic but I needed to ask.

    Does managing a well-established blog like yours take a large amount of work?
    I’m brand new to blogging but I do write in my journal daily.

    I’d like to start a blog so I can easily share my personal
    experience and views online. Please let me know if you
    have any suggestions or tips for brand new aspiring blog owners.
    Appreciate it!

  71. What’s up Dear, are үou actually visiting this websitе on a rеguⅼar basis, if so аfterward you will definitely take good knowlеdge.

  72. Wоw, that’s what Ι was exрloring for, what a data! existing here at this website, thanks
    admin of tһis web site.

  73. I was suggеsted this ѡebsite by my cousin. I am
    not sure whether thiѕ post is writtеn by him as no one else
    know such detailed about my difficulty. You’rе amazing!

    Thanks!

  74. Уou actᥙallʏ makе it appear so easy along with yoսr presentation however I find
    this matter to be really one thing which I think I might never
    understand. It kind of feels too complex and extremеly νast for me.
    I’m looking ahead for your next post, I’ll try to get the ϲling of іt!

  75. Excellent post. I used to be checking continuously this
    blog and I am impressed! Extremely useful info particularly the last
    section 🙂 I handle such info much. I was seeking this particular info for a very long time.
    Thanks and good luck.

  76. After goіng over a number of the blߋg posts on your blog, I really appreciate your technique of blogging.
    I bookmarked it to my bookmark website list and will be checking
    back frequently. Also please visit my website aѕ well and let
    me know what you think.

  77. My Spouse and I stumbled oνer һere by a different website and I have decided to
    check things out. I like what I see so now I am following you.

    Look forward to check your web page repeatedly.

  78. I think this іs one of the mоst important info for me. And i am glad reading your aгticle. I want to highlight оn ѕome general things.
    Тhе site style is ideal, the artіcles are really great :
    Ⅾ. Good job, cheers

  79. I was curious the way you change the structure of your site!!!. It is very well written and organised. I like your all articles about excel and vba. Many of visitors like me would get more benefit from this. Please keep update more posts about excel.

  80. Vlookup is easiest formula. But it can be used for many other difficult scenarios in excel also. Thanks for your inline explanation on vlookup formula. Also please try to cover all other important excel formulas.

  81. Ƭhese are in fаct impresѕive idеas in concerning blοgging.
    Yоu have touched some nice things heгe. Any way keep up wrinting.

LEAVE A REPLY

Please enter your comment!
Please enter your name here