What is VLOOKUP Formula & How to use it? [VLOOKUP WEEK]
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:
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
 
 

Leave a Reply
Celebrating the Lookup Formulas – VLOOKUP Week @ Chandoo.org  Making VLOOKUP formulas go wild [VLOOKUP Week] 
30 Responses to “What is VLOOKUP Formula & How to use it? [VLOOKUP WEEK]”
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!
[...] 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 […]