Bottom Line: Learn how the VLOOKUP function works with this simple explanation and example using a Starbucks menu. You can download an example file below to follow along.
Skill Level: Beginner to Expert
In this article you will learn the basic concepts of the VLOOKUP function. I provide a step-by-step guide on how to write a vlookup formula and you can download an Excel file to follow along.
Plus, you will never look at a restaurant menu the same way again… 🙂
Vlookups at Starbucks?
I was standing in line at Starbucks the other day, studying the prices on the menu, and I realized I was doing a bunch of vlookups in my head.
As I scanned down the items on the menu and then looked across to the price columns, I was doing the same thing a vlookup function does.
So I thought this might be a good way to explain the vlookup because everyone can relate to ordering from a food menu. And even if you're already a vlookup expert, this might help you explain the function to your co-workers.
Vlookup Defined
The job of the vlookup is to look for a value (either numbers or text) in a column. Once it finds a match, the vlookup will return a value from any cell in the same row as the match.
The Starbucks Menu Example
Let's look at the Starbucks menu as an example. When looking at the menu I decide that I want to order a Caffe Mocha. I also know that I want a size Grande. Now I need to determine the price to make sure I have enough money to pay for this expensive cup of coffee. 🙂
We naturally do this process in a series of steps in our head (see image above):
- First we look down the list of items on the left side of the menu until we find Caffe Mocha.
- Once we find the item, we then look across the columns to the right to find the price.
- The prices for the size Grande items are listed in the 3rd column of the menu, so we scan over to the 3rd column to find our price. There it is, $3.95 for a delicious cup of energy.
Vlookup Example in Excel + Download
Now we'll take a look at how this example works in Excel. I recreated the Starbucks menu in Excel for this example. You can download the file to follow along.
We want to answer the question: “What is the price of a Caffe Mocha, size Grande?”
We will use the vlookup function to answer this question in cell B14.
Vlookup Function Components
First we need to understand the components or arguments that make up the vlookup function. The following image shows the Excel definition of the Vlookup function, and then my simple definition. This simple definition just makes it easier for me to remember the four arguments.
I explain this simple definition below as we walk through an example of creating a vlookup formula.
Creating the Vlookup Formula
Now we will create the vlookup formula to find the price of the Grande Caffe Mocha.
1. lookup_value – This is the what argument.
In the first argument we tell the vlookup what we are looking for. In this example we are looking for “Caffe Mocha”. I have entered the text “Caffe Mocha” in cell A14, so we can make a reference to cell A14 in the formula.
We could also add the text “Caffe Mocha” (surrounded in quotes) directly into the formula.
=VLOOKUP(“Caffe Mocha”
But using cell references (A14) makes the formula easier to copy down and reuse for lookups on other items.
2. table_array – This is the where argument.
We need to tell the vlookup where to look for “Caffe Mocha”. This argument is specified as a range of cells, and the vlookup will only look in the left column of this range. We also need to include the columns that we want to return a value from. In this case we need to include the price columns because we want to return the price for our lookup_value.
So I reference the range $A$2:$D$10 in the second argument of the function. This means that the vlookup will look through all the rows in column A, from A2 to A10, in search of the value “Caffe Mocha”. Once a match is found it will then look to the right a specific number of columns to return a result.
3. col_index_num – This is the column number argument.
The column number tells the vlookup which column in the table_array we want to return as the result. It counts the columns from left-to-right based on the starting column of the table_array.
Since we want to know the price for the size Grande, I put the number “3” as the third argument of the function. This will return a result from the third column (column C) of the table_array.
4. [range_lookup] – This is the closest match argument.
Here we specify whether the vlookup should find an exact match or closest match to the lookup_value. By default, the vlookup will find the closest match to our lookup_value and return a result. This method is best used for looking up numbers.
However, for our example we want to find the exact match to the word “Caffe Mocha”, so I will put “False” for the range_lookup argument. This means that the vlookup will look for an exact match to the value “Caffe Mocha” in the first column of the table_array. If it does not find the word it will return an error (#N/A).
The Result – $3.95 for a Grande Caffe Mocha!
The vlookup formula returns the value 3.95 in cell B14 as expected. This formula can now be reused in other cells to look up other items in the menu. Or you could change the value in cell A14 to a different menu item, “Cappuccino” for example, to instantly see the price for the size Grande Cappuccino.
Conclusion – It's Time for a Coffee!
I hope this helps you understand the basics of how the vlookup function works. Vlookup is one of the most commonly used functions in Excel for looking up data, and it's a great one to know. This article only scratches the surface of what can be done with the vlookup function, but it's best to understand the basics before you learn more advanced techniques.
Most importantly, you now have a good excuse to go out for a coffee break next time you need to explain the vlookup function to a co-worker. 🙂
As always, leave a comment below with any questions or suggestions.
What's Next
This article is part of a series on lookup functions. Please checkout the other articles below.
- VLOOKUP & MATCH – A Dynamic Duo
- The INDEX Function – A Road Map for Your Spreadsheet
- How to Calculate Commissions in Excel with VLOOKUP
- How to Use VLOOKUP to Find the Closest Match – Last Argument is TRUE
Take the VLOOKUP Challenge
I've put together 5-part video training series that will help you learn more about lookup formulas and test your skills.
Thank you for another great and simple lesson!
Thanks Yelena! Glad you enjoyed it.
this information is useful to who don’
t know about excel ,before saw this videos i don’t about excel .but when saw this videos i know about excel thanks jon
Thanks Koti! 🙂
This VLOOKUP is a good suggestion.
By chance I noticed in your example that the search for a text match is not case sensitive.
Hi Dario – That is correct. The vlookup is NOT case sensitive. You could use “caffe mocha” or “CAFFE MOCHA” for the look_up value, and it would still return the same result.
You can use the EXACT function in combination with the VLOOOKUP function to find an exact match. The following Microsoft article has an example.
http://support.microsoft.com/kb/214264/en-us
You can also use EXACT with an Index & Match function.
Please let me know if you have any questions. Thanks!
Nice!! Very Creative
Thanks Sumit!
Jon,
This is a fun way to explain vlookup, I enjoyed it! I´ve been doing this subconsciously but now Im stuffed, every time I look at a pricing board I will be vlookingup the answer 🙂
Awesome! Thanks John!
Thanks Jon for the tutorial,hope I can get other formula explanation like (indirect,index and offset)using real situation examples like this one.
Thanks Sunday! That’s a great suggestion and I will plan more articles like this in the future.
Making the explanation about something as simple as the Starbucks menu is genius! This is the best explanation of VLookup I’ve ever seen.
Mike, you made my day! Thank you so much!
sir, what will be the formula if i want to write the order as “Caffe Mocha Grande” or “White Chocolate Mocha Tall” and i want it to automatically take the last word as column number?
Hi Dhiman – That’s an excellent question. You can use the MATCH function to lookup the size and return the column number. You will only be using the size for the lookup_value argument in the MATCH function, so you will need to extract the size from the full string of text.
In your example, you would need to extract Grande from the phrase “Caffe Mocha Grande”. This can be done with formulas, but it would be easier if you designed the model to account for this.
You could have one cell that contains the item “Caffe Mocha”, and an adjacent cell that contains the size “Grande”. Then reference the size cell in the MATCH function.
You will then use the MATCH function inside the VLOOKUP function for the col_index_num argument. It will look like the following.
=VLOOKUP(A14,$A$2:$D$10,MATCH(“Grande”,$A$1:$D$1,0),FALSE)
I am planning to write another post that explains the MATCH function and how to use it with VLOOKUP. It is a great function to know that will help prevent you from getting errors with your VLOOKUP formulas. Please let me know if you have any questions in the mean time.
For anyone else that has the same question, feel free to subscribe to my email updates and you will be notified when the new post is available. Thanks!
Genius in it’s simplicity 🙂 I want to use this analogy the next time I have to teach VLOOKUP! Or, my goto Vlookup alternative: intersections via empty space between named ranges, ie:
=Cappucino Grande
Thanks!
Thanks XSzil! I am glad you found it useful and best of luck with your teaching. I’m curious to learn more about your intersection technique.
You are most welcome, Jon. I don’t even remember anymore where I originally learned the intersection technique, but it’s one of my faves! Often a good alternative for casual Excel users who just can’t seem to get down with the VLOOKUP.
So. First I highlight the whole range, then with a little Formulas tab > Defined Names > Create from selection (top row / left column) action, you have your named ranges. Then we can of course use those names in formulas… and in this case, simply putting an empty space in between the two names, you get the intersection! E.g.:
=Cappucino Grande
returns $3.65
~ no VLOOKUP needed!
Very cool trick! I tend to avoid named ranges unless all the users of the spreadsheet are familiar with them, which is not the case very often. But they can be useful. Thanks for sharing!
That’s a great combination of tricks, Szilvia! The Create from Selection bit is one of my new favorites. And nice post, Jon!
Any chance you could break it down a bit more? I tried to follow your steps between the > but I must have missed something. I don’t speak fluent Excel but I sure would like to get this intersection option! Thanks so much for sharing!
Hi Jill,
Here is a screenshot with those instructions.
View Full Size Image
Please let me know if you have any questions.
Thank you
thank you so much ,your works is a great work
regards
Thanks Manou! 🙂
thank you Mr.Jon Acampora . Its too Useful..
Thanks Madhan!
OUTSTANDING example! For the first time I fully understand this function and can use it with desired results. Most times I tried I received an error message. So I ignored the value added of the function. Keep using this type example in your instruction. It works! Thanks. Dan
That’s awesome Dan! I’m glad to hear you’ll be using vlookups in the future. They can be a real time saver. Also glad to hear that you like the explanation. Thanks Dan!
[…] To demonstrate the point of being easy to learn, one can simply follow a very creative VLOOKUP tutorial . Using Starbuck’s menu as our data source, one can easily explain the concept of a VLOOKUP […]
Thanks Jon.
Your explanation of vertical lookup was excellently done – simply and clearly. I like!
Awesome! Thanks for letting me know Andrew!
I also agree; very well explained. What I would love to see is a list of common errors with vlookups and how to solve, eg:
– #ref (choosing an index_num that is higher then the amt of columns in the selection)
– N/A because of text formatted as number
– N/A because of leading/trailing spaces
etc
That’s a great suggestion Guido! I will put something together. I also have a webinar coming up that will go into more details on how to prevent errors with the lookup functions. I will be sending out an email to everyone subscribe to the newsletter when it’s available. Thanks again!
Hi Jon
I did a “V-Lookup” today, and as I was doing this, it came up with a error named “NAME”, is there a list of what errors can occur, what they mean & how to resolve them?
Thanks
Hi James,
The #Name error means that you typically have something misspelled. It could be that one of the ranges is not spelled correctly or has an extra character in it. This article has a good list of errors. Please let me know if you have any other questions.
Thanks!
Started a new job today and haven’t used the vlookup in a while, so I decided to brush up. This is a great example! Thanks!
Thanks Steve! Please let me know if you have any questions. And good luck with the new job! 🙂
A very nice explanation with real life example…:)
Thanks Apoorva! 🙂
Really appreciate this – a simple example on how to use the VLOOKUP command that I have not appreciated how to use. Thanks for the great example.
Thanks Jim! VLOOKUP can really help change how you work with Excel, and save time. Have a great day! 🙂
I like your explaination very much, simple and easy to understand =)
Thanks Eunice!
Dear Sir,
I am working in logistics field, where I have system data excel sheet with truck no & in separate excel sheet with vehicle no & vendor name. We are using 4 to 5 different vendors (Transporters) for transportation of containers. I want excel to mention vendor name in front of each truck no.
Thanks & Regards,
Shirish
[…] Jon Acampora explains vlookup using Starbucks menu […]
you make excel interesting to learn and use. thanks
This is a great example that many people can easily relate to, so glad you shared this, Jon! I used to get frustrated though, when given a VLOOKUP/MATCH formula example that shows a text entry because I couldn’t understand why someone would want to go to all the trouble to type the text in the formula, when the examples typically only had one possible correct response. My light bulb finally went on, and instead of typing GRANDE in the match formula, I typed SIZE in cell A15 and Grande in cell B15, and then replaced “Grande” in the MATCH formula with the absolute cell reference $B$15. Now I can enter the beverage and size I’m looking for and find the match. I feel like I’m ready for your next class so I can apply this in other areas! Thanks Jon!
Thanks Susan! I am so happy to hear that you enjoyed the articles. I will have more videos on VLOOKUP and the INDEX MATCH combo coming soon.
Thanks for the Vlookup function in Excel,it is very interesting and informative.
Keep up the good work.
Thanks Marcellin!
Hi Jon,
The way of explanation is really good. Thanks a lot!
Thanks Ajay! 🙂
I thought that a VLOOKUP was always an approximation. Great explanation.
I agree with Sunday about INDEX and the rest. It’s time I started digging in.
I found the ideas from the various comments quite interesting as well makes one think
Thanks a ton.
Thanks Wellsley! You are in luck. I am publishing a free video series on VLOOKUP & INDEX MATCH that will be out tomorrow. Here is the link to the first video if you want a sneak peak…
[…] VLOOKUP Example Explained at Starbucks post by Jon Acampora […]
[…] CHECK IT OUT […]
Great Explanation !!!!! very helpful
Thanks Mohd!
AMAZING EXPLANATION! YOU MADE MY LIFE EASIER! 🙂
THANKS Laura! You made my day! 🙂
I would have refered more than 100’s of online and book references to understand Vlookup.I thought it was the most dreaded thing in excel that would be asked in an interview but look how much easy you made it thanks Jon thanks a lot.Its the best possible way that vlookup could be taught and the best part is i just experimented the same concept of vlookup for hlookup which is just the other way around and i got that too.#yayiloveexcel
Thank you Sambit! I’m so happy to hear that the article helped you. Vlookup is a relatively complex formula that performs a simple task we do every day in the real world. 🙂 I love Excel too! Have a good one Sambit!
I thought Sambit had bad grammer, but then your response spelled hear (to listen) here (location). Pretty pitiful.
Yikes! Guess I’m not perfect after all. 🙂
And you spell Grammar as grammer 😀
What a great way to explain vlookup! love it
Thanks so much Linda! 🙂
the way you presented is impressive ,this is the way of teaching .I am glad to find this stuff,thanks alot
Thanks Gopala! I appreciate your kind feedback. 🙂
your explanation is very clearly.
Wow, this is impressive.!More understanding about V-lookup now,You indeed a good teacher!
Cheers
Awesome! Thanks Bridget! I’m happy to hear you enjoyed the video. 🙂
Hi sir Jon, nice post, very easy to understand, cheers, i hope you have also a simple tutorial for pivot table that is easy to understand like this, ty, God Bless
Thanks! And yes, I have a free 3 part video series on pivot tables and dashboards that will help get you started.
Awesome Job Jon in explaining clearly the VLOOKUP function
Thanks George!
Great example used to demystify what seem a complex aspect of excel into easy and straight forward steps.I Enjoyed it.
Thanks Aaron! I’m happy to hear you enjoyed it and learned something new. 🙂
THANKS! the VLOOKUP Starbucks sample was great! And now for a cup of tea.
Haha! Thanks Mrs. Kim. I’m glad it made you thirsty. 🙂
Thank you, your Vlookup article and your videos are very helpful.
Thank you Siddhiqua! 🙂
Thanks for this article! My class and I really found it helpful.
Awesome! Thanks Phill!
Hi Jon,
Again a great example although I still have a question.
If I write TRUE on the [range_lookup] why do I get 3.75?
Thanks,
Rui
Hi Rui,
When the range_lookup argument is TRUE, vlookup will return a closest match. Here is an article on how vlookup works when the last argument is TRUE. I hope that helps.
Thanks!
Thanks for the reply Jon 🙂
So it can be any of the $3.75 shown?
Rui
Vlookup will always return the FIRST matching cell value. This is also true when the last argument is TRUE for approximate match.
Hi Jon
I did a “V-Lookup” today, and as I was doing this, it came up with a error named “#N/A”, how to resolve them? I’m pretty sure that I have follow step-by-step your instruction and no NULL value in my table.
Hi Taufufa,
I have a free 3-part video series on the lookup formulas and the 2nd video is all about formula errors. I hope that helps.
Hi Jon,
The way you explained with example is awesome and thanks for input.
Found this very useful and easy to understand. Thank you!
Thank you Yinka! 🙂
This is the most easily explained and to the point explanation I have ever had.Thanks Jon you are amazing teacher.
Thank you so much Amino! 🙂
Thank you, you are grate, although i am slow in learning
Hi Jon,
First of great job with the tutorials. Question can vlookup be used to lookup and return data from a pivot table?
Hi Yitzhak,
Yes, you can reference a pivot table range for the table_array argument.
Unbelievably helpful. Thanks for this outstandingly easy to understand post!
Thank you Dave! 🙂