• 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. We’ve also just launched Working with Numbers.

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


    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.

    Two tables. Column B has the position of various runners. Column C has their names. Col. D is for their Prize money which we’ll look up from col. J, using col. H, which also has the runners positions (1-10)If 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)


    A screenshot showing the “insert function” window and the vlookup function highlighted as one of the “most recently used” functions.


    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.


    A screenshot showing the “insert function” window with all functions assorted in alphabetical order.


    A screenshot showing the “insert function” window with all functions but the vlookup function highlighted at the bottom


    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



    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.


    A screenshot showing the “function arguments” window and the lookup value populated with cell B2 (our first vlookup value).


    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. You’d then need to FIX this table array by putting dollar signs in before the H, before the 2, before the J and before the 11, so your formula at the end looks like this: =VLOOKUP(B2,$H$2:$J$11,3,FALSE). If you’re in the table array field and you press F4, then Excel will do this automatically for you. 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.


    A screenshot showing the “function arguments” window, the lookup value populated with cell B2 (our first vlookup value) and the data for the table array – columns H to J.


    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.


    A screenshot showing the “function arguments” window, the lookup value with cell B2, the data for the table array – columns H to J and the column index number – 3.


    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.


    A screenshot showing the “function arguments” window, the lookup value with cell B2, the data for the table array – columns H to J, the column index number – 3 and the range lookup field populated with the word “false”, which it should always be.


    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

    We ask that you take a moment to read our Terms and Conditions and Privacy Policies.

    306 Responses

    1. Gunjan says:

      Thank u so much……….
      Very helpful………

    2. moyin says:

      Wonderful explanation, kudos!!! I can use VLOOKUP now thanks to you.

    3. christin says:

      Thanks a lot…………….

    4. ABHISHEK says:

      Perfect when compared to the other sites!
      good going, simple and easy.

    5. klorvalex says:

      How do I combine vlookup with sum function?
      Suppose I vlookup Position 10 on the 2nd table,
      found it on the 3rd row and the prize is $100000, then I want to sum it with 1st & 2nd row. how do I do it?

      • Analyst says:

        Apologies for the delay. You can sum up data from different columns using vlookups, but not different rows. Not sure why you’d want to sum up the data in the way that you describe though? What’s the common factor in the query you have? If there is a common theme between the rows you describe eg same month, then you’d be better off using a pivot table.

    6. Sudeep Kr. Mazumder says:

      Thanks for the Best and superb description, but I have some more queries. I have three different tables in a same spreadsheet, all the tables contain different kinds of expenses of different months. Will it be possible to get the total sum of a respective month. As an example I want to get the total expenses of August month from Table1 (Telephone Expenses), Table2 (Electrical Expenses) and Table3(Miscellaneous Expenses) of August month in another spreadsheet of same file. Will it be possible with vlook and sum function? Kindly reply.

      • Analyst says:

        Hi Sudeep

        Apologies for the delay in responding. Yes, you can do that with a vlookup, but it would be much better to do it with a Pivot Table, as you can sum up the data for the respective months in a much easier to read format – I’ve created a simple tutorial on pivot tables which actual tells you how you can sum up data for different months for various expenses, which seems to be exactly what the nature of your query is! The tutorial is here: http://pivottablesinexcel.com/ If you still have questions, let me know.

    7. Avaldes says:

      thanks for the explanation best ever

    8. Arjun says:

      Thank you so much! Went through a few explanation sites before this and just couldn’t get my head around it. This really helped!

    9. Anita says:

      very good. very helpful

    10. Nitesh says:

      Good article. Very helpful!! Thank you.

    11. Michel says:

      It was awesome! Thanks a lot.

    12. Amal says:

      Many Thanks

    13. abhimanyu says:

      awsum man….thanks.

    14. Sherrie says:

      Best tutorial I’ve seen for vlookup. Thank you!!

    15. Bill Boswell says:

      I use vlookup daily but honestly don’t know how it works.
      I am comparing similar data in two separate spreadsheets, looking for matches. I am told that vlookup can do it with no problem.
      What I can’t do, is lose the information that doesn’t match. I am sure it is a simple process but I can’t figure it out.

      • Analyst says:

        Hi Bill

        When you say you want to ‘lose the information that doesn’t match’ do you simply want to return a ’0′ or a ‘blank cell’ instead of an #N/A? If so, you can do this using the formula I’ve described in problem 8 on this page: http://howtovlookupinexcel.com/10-common-problems-with-vlookups

        If you want to do something else, then drop me a line at the email address that you receive this email from.

    16. ajay m says:

      it was awesome…………

    17. Naresh Jain says:

      Nice tutorial for Vlookup and with the explanation so clear that I was able to solve the the error without any difficulty. Thanks once again.

    18. umasankar panda says:

      nice presentation

    19. vanitha says:

      Too good, understood at one shot…………..txs

    20. Sunny Chauhan says:

      Thank you sooooo much. I didn’t know Excel is sooo easy. All the credit goes to you coz u did it for us :-P

    21. Rise says:

      Thank You!!!

    22. Cassandra says:

      Was well understood and very easy to understand and use, hopefully I can get a refund for the $30 book I brought as I could not understand its teachings at all.
      Thanks again !

    23. Preeti says:

      Nice expirience to learn like this

    24. Deb says:

      Thanks so much this is exactly what I’ve been looking for. Your instructions are well put together as I didn’t make one mistake going through it. I didn’t even read the instructions prior to starting the steps

    25. sanjay says:

      Thanks a lot, it helped me a lot.

    26. Danni G says:

      Thanks a million! I have been trying to gasp this concept for over a year and most examples are very complicated.

      You should write a book! :) i’d buy it.

    27. Mullika says:

      Thank you so much, that’s helped alot.

    28. Akbar says:

      Excellent!!! really easy to understand.

    29. Mirshad Kanyana, Qatar says:

      Thanks so much sir,

      Please send me a personal e-mail.

    30. Dolphine says:

      Thanks!Excellent .

    31. Sandy says:

      Thanks so much…. u r a winner.

    32. Sandeep Chauhan says:


    33. Patricio says:

      Thanks man!!

    34. Ahmed Pasha says:


    35. Dana Chalberg says:

      The tutorial was a huge help. I also used the 8 common problems and got exactly what I needed from it. I’m using vlookup to display a name if/when an ID# is typed. The spreadsheet uses 50 rows, and only if/when there is data to track. The problem was that #N/A displayed regardless (i.e., without any ID#), visually cluttering all unused row. The ‘=IF(ISERROR(VLOOKUP(…’ function gave me what I wanted – a value of ’0′. I then used conditional formatting to suppress the zeros displaying – a blank spreadsheet unless an ID# was entered. Thank you!

    36. Karthik says:

      best way to explain

    37. Sudheesh Kumar says:

      Explained in a nice way.

      thank you..

    Leave a Reply