# What is VLOOKUP Formula & How to use it? [VLOOKUP WEEK]

Posted on November 1st, 2010 in Learn Excel - 39 comments

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.

 Celebrating the Lookup Formulas – VLOOKUP Week @ Chandoo.org Making VLOOKUP formulas go wild [VLOOKUP Week]
 Written by Chandoo Tags: downloads, Learn Excel, Microsoft Excel Formulas, spreadsheets, vlookup, vlookup week Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 39 Responses to “What is VLOOKUP Formula & How to use it? [VLOOKUP WEEK]”

1. 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!

• Sumeet Puri says:

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

2. [...] you have learned how to write vlookup formulas. You have also seen some pretty interesting examples of it (1, [...]

3. [...] 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 [...]

4. [...] 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 [...]

5. [...] have talked a lot about VLOOKUP and other lookup formulas recently during VLOOKUP [...]

6. John O'Connor says:

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?

• Heinrich says:

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.

7. [...] have used pivot tables, SUMPRODUCT, SUMIFS, INDEX+MATCH, VLOOKUP formulas to process the [...]

8. newexcell-or says:

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?

9. newexcell-or says:

Dear Chandoo,

I have an obligation of using Vlook-up, i can mix it with some other formulas. I thought of giving this a try using SUM, offset, vlookup, but did not succeed. 🙁

10. Chandoo says:

Why would you have such an obligation? VLOOKUP is not the formula for what you are trying to do...

11. newexcell-or says:

I have an assignment to be done...just vlookup..that can be mixed with other formulas also.

12. macourtney1 says:

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.

13. Omar says:

Great site!! Gracias!

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 every-time 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.

Thank you

14. Aziemah says:

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?

15. Jonathan says:

i only got the 1st one in the home work. I can only get the other 3 with helper cells!

16. Mangapathi says:

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

17. Hui... says:

@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

• Ashish says:

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,
MUMBAI

18. sNEHANGSHU SEN says:

THANK YOU SO MUCH...U R GREAT!!!!!

19. Jenikcarl says:

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.

20. Heinrich says:

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/Lookup-formulas/Vlookup-formula/Vlookup.php

21. [...] «??? ??????? ?????? ?? ???? chandoo.com» [...]

22. [...] «??? ??????? ?????? ?? ???? chandoo.com» [...]

23. […] now you know that VLOOKUP() cannot fetch values from columns to left. It does not matter if the person looking up is the star […]

24. […] Read more – What is VLOOKUP formula and how to use it? […]

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

26. Fareed Ahmed says:

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

28. Michael Paul says:

http://chandoo.org/wp/2010/11/01/vlookup-excel-formula/
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?

29. Warren Silva says:

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

30. MK Vishwakarma says:

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))

31. […] What is VLOOKUP Formula & How to use it? http://chandoo.org/wp/2010/11/01/vlookup-excel-formula/ […]

32. Rakesh says:

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.

33. Mobi says:

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!

 Celebrating the Lookup Formulas – VLOOKUP Week @ Chandoo.org Making VLOOKUP formulas go wild [VLOOKUP Week]