What is VLOOKUP Formula & How to use it?
I often tell my excel school students that learning VLOOKUP formulas will change your basic approach towards data. You will suddenly feel that you have discovered a superman cape in your attic. It is that awesome.
What does VLOOKUP really do?
Imagine you have a list of data like this:
Now, how do you answer the question – “How many sales did Jimmy make?”
Yes, your guess is right. VLOOKUP is one of the formulas you can use to answer questions like this.
VLOOKUP searches a list for a value in left most column and returns corresponding value from adjacent columns.
So, in our case, we need VLOOKUP to search for Jimmy and return the amount of sales he made from column 3.
VLOOKUP Syntax & Examples:
The syntax of VLOOKUP is simple:
=VLOOKUP( this value, your data table, column number, optional is your table sorted?)
Here is an example to get you started:
What is VLOOKUP & How to use it – Video
Check out this quick video to understand how to use VLOOKUP, how to use INDEX + MATCH functions, how to handle errors with VLOOKUP.
VLOOKUP Examples & Homework
I have made a small excel file detailing 4 VLOOKUP formula examples. The file also contains some home work so that you can practice this formula.
Download VLOOKUP Example Workbook
More information on VLOOKUP formula
 VLOOKUP Syntax & Uses – index card
 VLOOKUP formula – using it, examples & advanced uses
 Range lookup – finding the corresponding range for a value
 … more VLOOKUP tricks, examples & downloads
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.

Leave a Reply
« Celebrating the Lookup Formulas – VLOOKUP Week @ Chandoo.org  Making VLOOKUP formulas go wild [VLOOKUP Week] » 
42 Responses to “What is VLOOKUP Formula & How to use it?”
Chandoo,
Loved the homework. Is there a better way to do questions 2,3,4?
2)=IF(VLOOKUP("Jamie",$B$5:$E$17,3,FALSE)>VLOOKUP("Jackie",$B$5:$E$17,3,FALSE),"Jamie","Jackie")
3)=VLOOKUP("Jagjit",$B$5:$E$17,3,FALSE)/VLOOKUP("Jagjit",$B$5:$E$17,2,FALSE)
4)=VLOOKUP($G$17,$B$5:$E$17,4,FALSE)/VLOOKUP($G$17,$B$5:$E$17,3,FALSE)
They just seem kind of long to do simple tasks...
Perry
Thanks for the great posts!
Hi,
I was just going through "Top 10 Excel formulas for you". I used Networkdays and Workday as well as Vlookup. I have always faced trouble while using Vlookup formula. However, the examples provided by you were so very simple that it generated my interest to learning more and more about Vlookup and excel as a whole.
Thank you so very much for that!
Sumeet Puri
[...] you have learned how to write vlookup formulas. You have also seen some pretty interesting examples of it (1, [...]
[...] far we have seen what VLOOKUP formula is and how to put it to some nifty uses. Today, we will go one step further and learn how to do 2 Way [...]
[...] know that VLOOKUP formula is useful to fetch the first matching item from a list. So what would you do if you need 2nd (or [...]
[...] have talked a lot about VLOOKUP and other lookup formulas recently during VLOOKUP [...]
Chandoo  I am enjoying Excel school. My question about VLookup is as follows...I have a spreadsheet which is more or less a directory. The first column lists the last names of approx. 950 medical providers, the next column is first name, the next is credentials and so on. The providers phone and fax numbers, email addresses, specialty, office address, etc. are listed. I want to create a simple vlookup so that a person can type in the last name and the fax, phone, and email address are delivered. I've created the formula and it works...HOWEVER, it does not work when there is more than one provider with the same last name (for example, Patel, or Williams)...the formula only delivers results for the first instance of that name entered. Is there a workaround for this problem?
By using countif, concatenate and vlookup together, you can find more than one result with vlookup. You simply number the results and all will be given.
[...] have used pivot tables, SUMPRODUCT, SUMIFS, INDEX+MATCH, VLOOKUP formulas to process the [...]
Dear Chandoo,
My problem is very much similar to John O'connor. I have to summarize the value of the coloumn which matches the Vlookup value. Example: Col are as follows Salesman, Brand,Sales. Now if I have to calculate sum of sales done by a salesman XYZ using Vlookup. Can i do it?how?
@Newexcell... you should use SUMIF formula. Visit http://chandoo.org/wp/2008/11/12/usingcountifsumifexcelhelp/ for some examples and help.
Dear Chandoo,
I have an obligation of using Vlookup, i can mix it with some other formulas. I thought of giving this a try using SUM, offset, vlookup, but did not succeed. 🙁
Why would you have such an obligation? VLOOKUP is not the formula for what you are trying to do...
I have an assignment to be done...just vlookup..that can be mixed with other formulas also.
Is there a way a "layman" can display a bar chart or data bar in the background of a column of cells containing sales data to show what percentage of the budgeted revenue has been collected? The budgeted revenue is in another column. I currently have the percentages displaying in another column, and have used conditional formatting to see the data bar (with gradient fill) in the background of the displayed percent, but I want to eliminate the extar column and see the data bar in the revenue field.
[...] VLOOKUP Formula [...]
Great site!! Gracias!
Here it is my task:
I need to list a series of names related to a given number.
let´s say.... I have a list of names related to different numbers, since I want to use a drop menu everytime I hit any number from the list I want to call all the names related to that number.
B1:1 C3: John C4: Jennifer C5: Erick C6: Alma
B7:2 C:7 Jimmy C8: Boris
B9:3 C9: Mary
B10:4 C10 to C:18 More names...
in an other cel I will have a drop menu with the numbers so each time I select a number I want to see all names related to that number.
Please help!! is vlookup the right formula, ´cause it returns only one name...
Thank you
dear chandoo,
i need to do some checking of names from one sheet to another (i.e. whether the names on Sheet1 are missing from Sheet2). but the problem with vlookup is that it only works when the names are in exact match, and mine is not (e.g. in sheet1: John Lim, sheet2: Lim Yandi John).
in other words, the first, last and middle names are rearranged.
im wondering if there are any other formulas i can use instead of vlookup?
i only got the 1st one in the home work. I can only get the other 3 with helper cells!
Hi Chandoo,
2) =VLOOKUP("jamie",B5:E17,3,FALSE)>VLOOKUP("jackie",B5:E17,3,FALSE)
when I use this condition.. Answer is True. Meaning Jamie made more sales. Can you please tell me how to get the sales person name in the place of value "TRUE"
Thank you
@Mangapathi
=IF(VLOOKUP("jamie",B5:E17,3,FALSE)>VLOOKUP("jackie",B5:E17,3,FALSE),"Jamie", "Jackie")
Make sure when you copy it that you check the " characters as they don't translate well
Dear sir,
I am using Excel 2013 and in your formula i am getting error. I want name to be displayed instead of true
Thanks Boss !!!! i am very Happy because of my work is done for this formula.
Regards,
NAVNATH GAIKWAD.
MUMBAI
THANK YOU SO MUCH...U R GREAT!!!!!
Challenge #2: What is amount payable after accumulated quantity discount?
I am not able to solve the above challenge. I am pretty much a newbie. Any help will be greatly appreciated.
DO not use vlookups with numbers. They return errors and then you cannot sum your data. Rather use the sumif formula or alternatively use the iferror and vlookup in combination
http://www.31bestofexcel.com/Formulas/Lookupformulas/Vlookupformula/Vlookup.php
[...] «??? ??????? ?????? ?? ???? chandoo.com» [...]
[...] «??? ??????? ?????? ?? ???? chandoo.com» [...]
[…] now you know that VLOOKUP() cannot fetch values from columns to left. It does not matter if the person looking up is the star […]
[…] Read more – What is VLOOKUP formula and how to use it? […]
[…] another table where we list points in accordance to the Lines that we have already created. We will use VLOOKUP () to fetch the corresponding coordinate through this formula and we will do this for all the three […]
I want to ask somthing about some sheets
[…] Master the VLOOKUP – VLOOKUP is an Excel function that searches for values in a column of a spreadsheet list or table. The V in VLOOKUP stands for vertical (column). It allows you to pull data in from other places in your Excel sheet. A simple example is a spreadsheet containing thousands of part numbers, each with a description and a price. The VLOOKUP allows the user to enter a part number and immediately obtain the description and price. For more on VLOOKUP, visit this tutorial. […]
In your site...
http://chandoo.org/wp/2010/11/01/vlookupexcelformula/
under the caption...
VLOOKUP Syntax & Examples:
in the JPEG...
in the example it is written...
vlookup("John",list,2,false) = finds where Jon is in the list and returns the value in the 2nd column
But, Jon should be John.
Am I right?
I used:
1. VLOOKUP(G17,B4:E17,3,FALSE)
2. IF(VLOOKUP("Jamie",B4:E17,3,FALSE) > VLOOKUP("Jackie",B4:E17,3,FALSE),"Jamie",IF(VLOOKUP("Jamie",B4:E17,3,FALSE) < VLOOKUP("Jackie",B4:E17,3,FALSE),"Jackie","Same"))
3. VLOOKUP("Jagjit",B4:E17,3,FALSE)/VLOOKUP("Jagjit",B4:E17,2,FALSE)
4. (VLOOKUP(G17,B4:E17,4,FALSE)/VLOOKUP(G17,B4:E17,3,FALSE))*100
I used:
1. =VLOOKUP($G$17,$B$5:$E$17,3,FALSE)
2. =if(VLOOKUP("Jamie",$B$5:$E$17,3,FALSE)>VLOOKUP("Jackie",$B$5:$E$17,3,FALSE),"Jemie","Jackie")
3. = (VLOOKUP("Jagjit",$B$5:$E$17,3,FALSE)/VLOOKUP("Jagjit",$B$5:$E$17,2,FALSE))
4.=(VLOOKUP($G$17,$B$5:$E$17,4,FALSE))/(VLOOKUP($G$17,$B$5:$E$17,3,FALSE))
[…] What is VLOOKUP Formula & How to use it? http://chandoo.org/wp/2010/11/01/vlookupexcelformula/ […]
I have 2 sheets with below data
sheet1:
id value
1
3
2
sheet2:
1 A
2 BB
3 C
vlookup used: =VLOOKUP(Sheet2!A2,Sheet2!A2:B4,2,0)
Result:
1 A
3 BB
2 C
Expected:
1 A
3 C
2 BB
I am looking for exact match for the value being searched. However, it is giving me values as per sequence.
Any help would be appreciated.
Hello,
I'm trying to do a vlookup to find cusip (from look up Table) in the example below, but the Cusip doesn't pull since the value has extra word "CORP" in the 1st cell. Would you be able to let me know if there's a better formula that may work in a situation like this? if the value doesn't match exactly.
Credit Facility Cusip
COMPUWARE 65 =VLOOKUP(A2&B2,TABLE!A:D,4,FALSE)
Look up TABLE: (4 cells)
COMPUWARE CORP65  COMPUWARE CORP  65  AB987654
Thanks!
I actually avoid VLOOKUP at all costs since I've mastered INDEXMATCH. Are there times when the advantage would be to use VLOOKUP?
Thank you so much Chandoo! I love wht=at I have learned today!
[…] Master the VLOOKUP – VLOOKUP is an Excel function that searches for values in a column of a spreadsheet list or table. The V in VLOOKUP stands for vertical (column). It allows you to pull data in from other places in your Excel sheet. A simple example is a spreadsheet containing thousands of part numbers, each with a description and a price. The VLOOKUP allows the user to enter a part number and immediately obtain the description and price. For more on VLOOKUP, visit this tutorial. […]