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

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

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.

Dear Jon,

I am unable to understand what “closest match” means. For example why would we look for closest match in case of Caffe Mocha when we have the exact match. I am not limiting my query to Caffe Mocha only. After all there is a corresponding cell in the same row which is filled, then why closest match.

thanks in advance

best regards

Sunil Munshi

Hi Sunil,

Checkout my article on VLOOKUP with Closest Match for an explanation on this. Thanks!

Thanks for help. It is helpful learning.

Hi JOE

Thanks a lot

Hi Dear,

Great Job.

Resource are helpful.

it is helpful

Keep it up

hi jon,

your easy approach helps me to understand the concepts clearly and encourages me to learn more to become an excel expert. thanks a lot. keep it up.

hi jon,

your easy approach helps me to understand the concepts clearly and encourages me to learn more to become an excel expert.

thanks a lot. keep it up.

My formula is not running. It has all the values correct. When I click enter, nothing happens.

=VLOOKUP(A2,$D$2:$D$2348,4,FALSE)

what am I doing wrong?

Avi

Hi Avi

May I ask what Cell this is going into (Display)

I tried a similar one, and found that if its in A2 (Your “What”) Then that would be why you’re getting an error, as the what needs to already have a value, so maybe just put the value your after in a cell somewhere and have that as your “What”, if that’s what you’re doing however then I’m also stumped

Hope this Helped

Blake

Hi Avi

May I ask is the What you have there the same as the destination? I.e. you are asking for a value in the same place as where you want the number displayed?

e.g.

A2 = where you want the value to be displayed

If this is the case then simply enter the value you want to find (lets say 7) in a different cell, (nearby for reference), then change the “What” to that cell

Hopefully that should fix the issue if not then I am also stumped.

Here’s what I did

D16 = 2

D17 = 3

D18 = 5

D19 = 4

F18 = 5

F19 = (=VLOOKUP(F18,D16:D19,1,False))

The other issue is that there is only 1 Column in the argument and you’re asking for the forth, in this 4 doesn’t = D, 4 = 4th column (If you have D-G, 4 will be G not D)

Hope this helped, I only stumbled across this 10 mins ago so sorry I couldn’t help sooner

Blake

Thank you for the detailed explanation Blake! Avi, as Blake mentioned, the Col_Index_Num argument needs to be changed to a 1. Since there is only one column references in the Table_Array (column D), then this formula will lookup the value in column D and return the value from the same column.

If you just want to check if a value exists in a column, we can use the COUNTIF function instead of VLOOKUP.

I hope that helps. Thanks!

Hi Avi, the Col_indexnumber ie the third part of the formula is wrong because you have asked the function to check only 1 column and you are asking it to return the value in the 4th column, which doesn’t even exist for the function. Please put 1 instead of 4 in your formula and it should work fine. 🙂

Hi Avi, the Col_indexnumber ie the third part of the formula is wrong because you have asked the function to check only one column and you are asking it to return the value in the 4th column, which doesn’t even exist for the function. Please put 1 instead of 4 in your formula and it should work fine. 🙂

Jon – You’re a hero – I have struggled for years understanding how to do VLOOKUPs when I need them (about every 18 months).

I wish MSFT would build your terminology into excel (What, Where, Range, Result)

You’ve made the inscrutable, ‘scrutable’.

Thank you so much Christian! The argument names in the function are definitely a bit confusing… 🙂

Hello everyone reading this, the one thing I noticed that you missed or skipped over Jon was that you can also use this over multiple sheets by adding ‘Sheet Name’! intothe formula after the first comma before Where Cell Numbers.

Hope it helped

Blake

Thank you Blake! Yes, this was just an introduction to VLOOKUP. We can use it to return values from other sheets or other workbooks.

Thanks Jon, you are a HERO for translating the Microsoft Office language into my mother tongue (english is my 4th language, i struggled to understand the MSFT terminology). today is my first time to grasp a bit about vlookup. Thanks

Thank you for the nice feedback Joshua! I’m happy to hear it helped. 🙂

I am new here but looking for info on if I can do a V lookup to calculate on multiple values..for example, commission calculated at varying rates based on tiers of sales volume? Ex…1% of first 50 k of sales, then 2% on the next 150k of sales, 3% on next 150k of sales

Thanks!

Hey Lisa,

Sorry, I just saw your comment here. I have another article on How to Calculate Commissions in Excel with VLOOKUP. This method uses a closest match VLOOKUP formula where the last argument is TRUE. I hope that helps. 🙂

Hi Jon,

I had question that how to calculate the salary increment, when there is conditions applicable for example, if basic salary is

Create a field Increment based on following condition(Using Vlookup):

Salary Increment

>=20000 20%

>=10000 and =5000 and <10000 8%

I tried a lot but I could not get it. please help

I use a bit of vlookup already but this explanation nails it so well.Am glad I came accross your page.will use it to advance my skills

Hi Jon, great tutorial. Do you have a link to questions/answers for vlookup questions using different data-set scenarios?

The explanation of the Vlookup application is great! Very helpful!

Thanks for this explanation Jon. For some reason, I can’t help to think that for each example I read I could simply search in excel for the lookup value (in the left column like Cafe Mocha) and simply look to the right in one of the columns. I know that is a very trivial and simple way to complete the above situation but in reality, it would have the same result fairly quickly. Can you help give an example that debunks this use case?

Hello Lewis,

There are many situations in which that would not work. Let’s say you have 2000 rows of data and you want to transfer a certain value from one worksheet to another. This is a new set of data, and there’s a specific value you need to copy over from the old data to the new. Instead of writing in the values manually one by one, you can use a VLOOKUP function to find the value you’re looking for and automatically populate the sheet with one formula.

What if in the main column to look up, there were 2 café mochas listed? This won’t happen with text, but what if there were numbers in that column, say two 17s? Does it default to the first one? Should you only use lookup if each item in the lookup column is distinct?

Thanks.

Hello John, thank you very much for the excel tutorials. I greatly appreciate. The tutorials are really helping me very much.

this blog is very useful and easy to understand.

I’m very grateful to you for helping me understand Vlookup function, as it is something i have struggled to understand.I can now go to my office and assist my colleagues.

Hi,

Can you please me to calculate salary using Vlookup?

Thank you for the refresher course, if you don’t use it you lose it and it’s always helpful to see a great version of instructions.

Thanks Jon, you are advancing my skills. I really appreciate your handouts

Hi Jon,I was unable to use the $ sign with my table array using F4 on my keyboard. I am using a laptop. Could you please tell me how else I could anchor my table array?

Thank you so much i am really impressed. Thanks again. Please keep posting good videos and information

This was incredibly helpful. Thank you for this easy to understand explanation and example!

how to vlookup from generated data?

THANK YOU!! I was trying to figure out VLOOKUP and I love your Starbucks Menu analogy. Finally clicked!

Does anybody know a formula to extract data from 5 + E,F,G etc please..

This works for column 1 – 4 =VLOOKUP(“LEERI”,Sheet2!$A$2:$D$21,4,0)

but I cant get anything to work for 5… =VLOOKUP(“LEERI”,Sheet2!$A$2:$D$21,5,0)

very nice way of describing! really appreciable!! This is the very first time i’m learning vlookup and really got a very good idea of it. Thanks!!!

Brilliant!! now it is too easy for me to learn Vlookup after some difficulty.

Very well done!

This has been the most helpful vlookup tutorial I’ve read. Thank you for taking the time to make it!

Thank you for using something simple that I could relate to! I had performed a Google search and had visited countless pages and listened to YouTube videos trying to simplify something that I didn’t understand really well. I knew the basic concept, but something at each site was not resonating with me. Now I know it so well that I could actually relay it to my students in simple fashion. Thank you very much!

Thanks Jon, your way of explanation was excellent. It helped me to remember the formula forever.

Really simple way to explain

You are one of the best teachers. Something very difficult once taught in a simple way will always be understood.

I very well understand how to use Vlookup now!!

Jon,

Love the way you explained the vlookup function.

Thanks

This is an excellent basic way of explaining Vlookups to people that don’t use them that often. Thank you for the clear and simple way to understand.

sir Jon, your way of explanation was excellent. It helped me to remember the formula forever.

Jon, your way of explanation is awesome, even i am learning online, i caught the correct way to understand from you. God bless you a million ways.

I will not add to the thanks below.

I tested the command after inserting a table to include the data.

Then I referred to the table in the command.

=VLOOKUP(“Mocha”,Menu,3)

It worked.

What if the lookup_value exists more than once?

Could I use this fuction to return results from more than one column?

NB that would only work if the items were in alphabetical order; if not, you need to include the fourth argument as “…,FALSE)”, “…,0)”, or even just “…,)” – they all work the same

If the lookup_value exists more than once, then it will find just the first occurrence

With the Dynamic Array functionality in Office 365, you can return results from multiple columns, eg:

=VLOOKUP(“Mocha”,Menu,{2,3,4},)

would return the prices for all 3 sizes

Very nice simple to understand.

Hello Jon

I have just discovered your website, and thank you, it really is great. May I ask if it is possible to do VLookup, if there are 2 Lookup_values which require only 1 result, without concatenating the 2 Lookup_values first (as a helper)?

Thank you.

Your VLOOKUP explanation is fantastic! Clear, clean, and simple!

nice analogy

…but you could have also used it to highlight the pitfalls of using VLOOKUP

eg

always looking in “the third column” rather than “the one headed Grande” (what if they introduced a “Media” size?)

or

what if you want to answer “which Venti drink is priced at $4.40 ?” (ie you can’t look left)

Hi, I want to make ethelphy chart (P-h diagram any gas) in Excel and calculate ethelphy through pressure and temperature. How draw this chart. (Example.. R134 refrigerant p-h diagram). I have reading of temp. And pressure in bar. How automatically put in chart and calculate ethelphy.

[email protected]