VLOOKUP Example Explained at Starbucks - Excel Campus
129

VLOOKUP Example Explained at Starbucks

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

VLOOKUP Explained at Starbucks Large

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. 🙂

VLOOKUP Explained at Starbucks Steps 1-2-3

 

We naturally do this process in a series of steps in our head (see image above):

  1. First we look down the list of items on the left side of the menu until we find Caffe Mocha.
  2. Once we find the item, we then look across the columns to the right to find the price.
  3. 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.

VLOOKUP Explained In Simple Terms At Starbucks.xlsx (10.6 KB)

Excel VLOOKUP Starbucks Menu Example in Excel

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.

Excel VLOOKUP Simple Definition

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.

Excel Vlookup Explained Lookup Value What

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.

Excel Vlookup Explained Where - Table Array

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.

Excel Vlookup Explained Column Index Number

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.

Excel Vlookup Explained Closest Match

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!

Excel Vlookup Formula Result Explained

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.

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here.
My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career.
I’ve been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP.
I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I’m not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 129 comments
Dave - February 15, 2017

Unbelievably helpful. Thanks for this outstandingly easy to understand post!

Reply
Yitzhak - January 23, 2017

Hi Jon,
First of great job with the tutorials. Question can vlookup be used to lookup and return data from a pivot table?

Reply
Abdallah - December 7, 2016

Thank you, you are grate, although i am slow in learning

Reply
Amino - December 5, 2016

This is the most easily explained and to the point explanation I have ever had.Thanks Jon you are amazing teacher.

Reply
Yinka - November 10, 2016

Found this very useful and easy to understand. Thank you!

Reply
Veera - November 8, 2016

Hi Jon,

The way you explained with example is awesome and thanks for input.

Reply
taufufa - October 30, 2016

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.

Reply
Rui - October 20, 2016

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

Reply
Phill McCraken - October 17, 2016

Thanks for this article! My class and I really found it helpful.

Reply
Siddhiqua - October 7, 2016

Thank you, your Vlookup article and your videos are very helpful.

Reply
Mrs. Kim - September 13, 2016

THANKS! the VLOOKUP Starbucks sample was great! And now for a cup of tea.

Reply
Aaron - August 31, 2016

Great example used to demystify what seem a complex aspect of excel into easy and straight forward steps.I Enjoyed it.

Reply
George George - August 10, 2016

Awesome Job Jon in explaining clearly the VLOOKUP function

Reply
x¥€mpi£ights¥x - August 9, 2016

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

Reply
Bridget - August 3, 2016

Wow, this is impressive.!More understanding about V-lookup now,You indeed a good teacher!

Cheers

Reply
umar - July 11, 2016

your explanation is very clearly.

Reply
gopala - June 22, 2016

the way you presented is impressive ,this is the way of teaching .I am glad to find this stuff,thanks alot

Reply
Linda - June 1, 2016

What a great way to explain vlookup! love it

Reply
Sambit Mohapatra - February 26, 2016

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

Reply
    Jon Acampora - February 26, 2016

    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!

    Reply
      Lynne - March 20, 2016

      I thought Sambit had bad grammer, but then your response spelled hear (to listen) here (location). Pretty pitiful.

      Reply
Laura - February 10, 2016

AMAZING EXPLANATION! YOU MADE MY LIFE EASIER! 🙂

Reply
Mohd Mustaqeem - January 23, 2016

Great Explanation !!!!! very helpful

Reply
The Dangers of Binning in Excel | My Spreadsheet Lab - December 12, 2015

[…] VLOOKUP Example Explained at Starbucks post by Jon Acampora […]

Reply
Wellsley - November 30, 2015

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.

Reply
Ajay Shankar - November 28, 2015

Hi Jon,

The way of explanation is really good. Thanks a lot!

Reply
Marcellin HABIMANA - November 25, 2015

Thanks for the Vlookup function in Excel,it is very interesting and informative.
Keep up the good work.

Reply
SusanM - November 24, 2015

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!

Reply
    Jon Acampora - November 28, 2015

    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.

    Reply
John - November 24, 2015

you make excel interesting to learn and use. thanks

Reply
How to use vlookup formula in Excel | My Spreadsheet Lab - October 21, 2015

[…] Jon Acampora explains vlookup using Starbucks menu […]

Reply
Shirish Patil - June 29, 2015

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

Reply
Eunice - June 3, 2015

I like your explaination very much, simple and easy to understand =)

Reply
Jim Martin - April 27, 2015

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.

Reply
Apoorva Nangia - April 22, 2015

A very nice explanation with real life example…:)

Reply
Steve - March 31, 2015

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!

Reply
JAMES - February 6, 2015

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

Reply
    Jon Acampora - February 6, 2015

    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!

    Reply
Guido - January 14, 2015

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

Reply
    Jon Acampora - January 15, 2015

    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!

    Reply
Andrew - January 13, 2015

Your explanation of vertical lookup was excellently done – simply and clearly. I like!

Reply
Ramaraju - December 17, 2014

Thanks Jon.

Reply
VLOOKUP() vs. INDEX(MATCH()) debate continues…Excel Strategies, LLC - November 20, 2014

[…]    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 […]

Reply
Dan - September 26, 2014

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

Reply
    Jon Acampora - September 28, 2014

    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!

    Reply
Madhan - July 9, 2014

thank you Mr.Jon Acampora . Its too Useful..

Reply
manou - June 30, 2014

thank you so much ,your works is a great work
regards

Reply
XSzil - May 20, 2014

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!

Reply
    Jon Acampora - May 21, 2014

    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.

    Reply
      XSzil - May 21, 2014

      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!

      Reply
        Jon Acampora - May 21, 2014

        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!

        Reply
        Ben Kusmin a/k/a Excel Esquire - November 3, 2014

        That’s a great combination of tricks, Szilvia! The Create from Selection bit is one of my new favorites. And nice post, Jon!

        Reply
        Jill - November 19, 2014

        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!

        Reply
Dhiman Sarkar - May 18, 2014

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?

Reply
    Jon Acampora - May 19, 2014

    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!

    Reply
Mike Alexander - May 15, 2014

Making the explanation about something as simple as the Starbucks menu is genius! This is the best explanation of VLookup I’ve ever seen.

Reply
Sunday - May 15, 2014

Thanks Jon for the tutorial,hope I can get other formula explanation like (indirect,index and offset)using real situation examples like this one.

Reply
John - May 15, 2014

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 🙂

Reply
Sumit Bansal - May 14, 2014

Nice!! Very Creative

Reply
Dario - May 14, 2014

This VLOOKUP is a good suggestion.
By chance I noticed in your example that the search for a text match is not case sensitive.

Reply
    Jon Acampora - May 15, 2014

    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!

    Reply
Yelena - May 14, 2014

Thank you for another great and simple lesson!

Reply

Leave a Reply: