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

Posted on November 1st, 2010 in Learn Excel - 30 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:

Sample Data - VLOOKUP Excel Formula

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 Excel Formula - Syntax and Examples

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 Week @ Chandoo.org - Learn tips on lookup formulas in Excel

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

30 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!

  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!

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

    Please help!! is vlookup the right formula, ´cause it returns only one name…

    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

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

Leave a Reply