• Do you want to use Excel more efficiently?

    Do you want to be able to go home earlier?

    If you answered yes to either of these questions, then read on.

    But before we proceed, many of you have been asking for a step by step tutorial on Pivot Tables, which is now ready here: Pivot Tables in Excel

    The vlookup is the most useful, yet most mis-understood function in Excel. 

    This is a step by step vlookup tutorial (only 7 steps) suitable for beginners. It’s made more interesting by including Usain Bolt in the data, as it’s the quickest (and simplest) way to do a vlookup!! This method works in Excel 2003, Excel 2007, and Excel 2010 (you just need to find the fx button above column B as demonstrated in the example below).

    LEARN HOW TO USE THE VLOOKUP FUNCTION IN EXCEL AND SAVE YOURSELF HOURS WITH ANALYSIS!!

    vlookups are usually done between different spreadsheets, but to make it easier to show how they work, I’ve put the data on to one spreadsheet (see image below).

    The tables in the spreadsheet have some of the most famous 100m runners in the world. They are numbered from 1 to 10 in column B (the numbers are for illustrative purposes only).

    I’ve put the names of the runners / athletes in column C.

    IMPORTANT: You should note that the table on the LEFT HAND SIDE is set in ASCENDING NUMERCIAL order ie numbers 1 to 10.

    However, the table on the RIGHT HAND SIDE is arranged in ALPHABETICAL order of the names of the athletes. So the numbers in that table DO NOT go from 1 to 10 in ascending order.

    We’ll use the vlookup function to get the ‘Prize Money’ each athlete has won in column J in the second table into column D in the first table.

    Image_2007_1If you want to actually test out the instructions below with exactly the same data in Excel, then we have included a link to the file with the data here -

    vlookup specimen file – Usain Bolt, etc.

    STEP 1

    First, click into cell D2 – as this is where we want the first vlookup result to appear.

    STEP 2

    Click on the ‘fx’ button above column B – many people start by typing “=vlookup…” but you don’t have to! Clicking the “fx” button is much quicker!

    (you will see the ‘Insert Function’ table pop up – screenshot below)

     

     

    From this window, we will select the vlookup function, as it is one of the ‘most recently used’ functions in the category here.

     

    If the vlookup function isn’t one of the ‘most recently used’ functions, then change the “Or Select a Category” field to ‘All, then scroll to the bottom, where you will see the word ‘vlookup’ and click on it.

     

     

     

    Click ‘ok’ and the function arguments window will pop up. This table will show the Syntax (a fancy word for the format of the formula) and it will display the different parts of the syntax ie

     

    VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

    STEP 3

    Here, you should click into cell ‘B2’ because that’s the first number or reference for which you want to lookup a value. The ‘look up value’ is the corresponding value we want to find in the first column of the second table – so we want to find out what ‘Prize Money’ the runner in position 1 got in the table that spans columns H to J.

     

     

    STEP 4

    Now click in the next field, the ‘table array’ field (the table array consists of two or more columns of data and the first column in the table array – in this case col. H – has the corresponding numbers that the ‘lookup value is looking at ie the runner positions in this case).

    STEP 5
    After clicking in the ‘table array’ field, you then need to highlight the columns that that you want to look up data from; so here, we will highlight columns H to J, because our lookup value column starts from column H in the second table, and the Prize Money which we want to check is in col. J

    An alternative way to populate the “Table_array” field is to highlight the range of data you’re looking up, starting with your first unique value – in this case cell H2. So you’d highlight cells H2 to J11, because J11 is the last cell in the range. This is useful to know if the spreadsheet doesn’t allow you to highlight cells H to J because some cells are merged or you’re getting an invalid error. But for now, just leave it, ensure, you’re fields look the same as those in the screenshot above then proceed to Step 6, the second last step.

     

     

    STEP 6
    When you highlight the columns, you should note that column J is the 3rd column from column H. We then move onto the next step, Col_index_num, which is the column Index number. Click in that field and type the number 3) – this is because column J is three columns away from column H.

     

     

    STEP 7

    Now, for the last bit, simply click in the ‘range lookup’ field and type in the word false. You should always type in the word false here, because we want the vlookup to return an exact match for what we‘re looking for, and if it doesn’t then we want it to return the word false.

     

     

    Now click ‘Ok’, and like magic, you will notice that the vlookup has returned the figure of $1,000,000 against Usain Bolt’s name in the first table. If you look at the second table, the figure of $1,000,000 is also against Usain Bolt’s name, so we know the vlookup has worked. And that’s how you string together the vlookup formula in excel.

     

    Now all you have to do is drag down the formula, so that the cells below cell D2 populate, and you save yourself the hassle of manually finding the corresponding Prize Money value for each and every athlete in the first table. In a work scenario, if you had hundreds of rows of data, this would literally save you hours of work!

     

    IMPORTANT point to note – the numbers in the “lookup value” column MUST precede the data you’re looking up! The same is true for the table that you are looking up data from (you must ALWAYS move from the left to the right, when using vlookups!)

    Congratulations on finishing the tutorial! If you want to try out a vlookup between two separate workbooks, you can do so here: http://howtovlookupinexcel.com/vlookup-between-two-workbooks

    If you have problems with your vlookup click here– 6 common problems:

     

    Subscribe via RSS

    235 Responses

    1. Sajid Malik says:

      Simple explanation, thank you

    2. vinod says:

      All I wanted to say is a Big Thank you.
      I have been asking my colleague to help and also googled but did not get a good one like this.

      Thank you

    3. susie says:

      I was struggling w/ Vlookups and then found your tutorial. Super useful! Thank you thank you!!!

    4. Shaheen shaikh says:

      Thank u very much, its really useful and helped me in my work. Once Again.. Thank u

    5. Mangal says:

      Very useful, thanks a lot

    6. Ravi Bhandari says:

      Great job.Thanks.Very well done.

    7. Anne says:

      Thanks,its really educative

    8. Capital G says:

      Its a really good job done explaining in so much details that anybody can understand. I find it very useful. Can I get the list of all such how-tos from you?

      • Analyst says:

        Ha! Thanks for the compliment dude – I’ve only done “how to’s” on vlookups and pivot tables at the moment.

        However, if there is another function in particular that you want covered, let me know and I’ll do a tutorial for it!

        Analyst

    9. ManiKumar says:

      Really Useful

    10. Eugene says:

      Brilliant!!! Took me less than 10mins to understand how to use VLOOKUP in a single spreadsheet and on two spreadsheets.

      Thanks very much sir. I noticed you are in the UK in one your comments. I have a Data Analyst interview tomorrow.

      Hope it goes well and if you are in the London area I owe you a pint of beer at Shoreditch.

    11. Abilash says:

      Awsome job!!!!!!! keep going.

    12. Fel says:

      I have used your example. it is very useful. do you have more examples to download to try.

      Thanks a million for your tutorial.

    13. Rashid says:

      thanx a lot buddy

    14. Asvad Mithani says:

      Outstanding and Perfect

      Thanks

    15. PRANKY says:

      PRECISE AND EFFECTIVE. BRILLIANT !!

    16. Manoj says:

      Thanks a lot

    17. uday says:

      its very awesome

    18. Ron says:

      This is the quickest i have come across !!!! You are a big help. If you have something for H Look up please let me know

    19. Mike says:

      I do have 1 question. I have a list 100 file extensions which I want to pluck out the amount of files of type (hence the vlookup). I am not expecting all file types to be in the sheet I am looking up in and if the type is not there the vlookup returns #N/A.
      Is there a way of telling the lookup to return nothing if the value it is looking up does not exist?
      The reason I ask is because it messes up the count if #N/A appears in the list. I then need to go in the sheet and clear the formula, I have 11 different sheets running the lookup so I need to clear #N/A on 11 sheets

    20. Mike says:

      Brilliant, this explained everything simply (it needed to be)
      many thanks
      Mike

    21. Terry says:

      Why cant you vlookup on b3? When I try to use text, I get N/A or the wrong answer.

      • Analyst says:

        You’ve probably done something wrong somewhere.

        The tutorial advises you start with B2 then simply drag down the formula. If you send me the file with the formula you have used, I’ll let you know where you went wrong.

    22. Biju says:

      It’s really a simply way to understand. Thanks a tonnnn for a valueable information.

    23. sheraz says:

      Dear Admin,
      Thank you so much for your useful tutorial.

    24. np says:

      Thanks for this tutorial .Many tutorials are there but this was the best one thanks again for this.

    25. David says:

      Nice explanation thank you.
      Is it possible to use VLOOKUP (or another function)to look for partial matches in the lookup column and display data (e.g. surname) for all the matches(e.g. look for “Jan” and get the surnames for Jan, Janet, Jane, etc)?

      • Analyst says:

        Hi David

        I don’t think it is, but you can get around that by using the auto-filter and simply selecting the first 3 letters eg “Jan” to return everything that contains those letters in a given column.

    26. Pradeep Rath says:

      Thanks for giving such a friendly example.

    27. lucy says:

      Excellent instructions, worked in 3 mins thank you very much.

    28. VIKASH RANJAN says:

      It’s so useful for beginners.nice

    29. JAISHREE JAIN says:

      THIS IS WONDERFUL . THANKU SO MUCH.

    30. P L Rane says:

      Thank You….

    31. S R Perumal says:

      Thank you very much for the user friendly explanation .. Helped a lot in my understanding …. Regards

    32. William Taylor says:

      I had great difficulty following the VLOOKUP example in Excel 2007 but you did it far better and much more easily! Thank you very much for putting your knowledge online to help others less gifted. best wishes, William


    Leave a Reply