Do you want to become a vlookup expert?

Do you want to save time looking up data at work?

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

The vlookup is the most useful, yet most mis-understood function in Excel! Learning the best ways to vlookup in Excel 2003 is easy though.

This is a step by step vlookup tutorial (only 7 steps) made more interesting by including Usain Bolt, as it’s the quickest (and simplest) way to do a vlookup!! This method works in Excel 2003, Excel 2007, Excel 2010 and Excel 2013 (you just need to find the fx button above column B 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 sets of data on to one spreadsheet in the table in the first image below.

The table has 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 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

Now 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! It types that bit for you!

(you will then 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 click on the “Most Recently Used” field shown in the screenshot above and change it to ‘All’. Sroll 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 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.

 

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

 

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

 

Subscribe via RSS

Disclaimer: The information provided on this site is for illustrative purposes only and we do not guarantee its’ accuracy. We also ask that you take a moment to read our Terms and Conditions and Privacy Policies.

 

Found our site useful? Please use this link to spread the word. http://bit.ly/vlookhelp

20 Responses

  1. Vijay Kumar says:

    Please let me know how I can drag the formula into all the columns. It worked in the first colum.
    But I don’t know how I can use the same formula in rest of the columns automatically without entering manually in each column.
    Looking forward for your response soon.
    I really appreciate your time. Thank you so much.
    Vijay

    • Analyst says:

      Hi
      If you want to drag the formula to separate columns, then you’ll have to fix the column with your lookup values AND fix the range you’re looking up, which is explained in the alternative section in step 5.

      It will be just easier to create a vlookup in the new column that you want it to appear.

      If you have lots of new columns where you want to create vlookups and your file doesn’t contain confidential information, then send it through and I’ll have a look.

      Analyst

  2. Mary says:

    Thanks for step 5 — I would get so far and the table array would move as I pasted down the formula.. problem solved.

  3. ASMITA says:

    Very useful material.. Thanks!!

  4. Costa says:

    Step 7 gave me the help I needed! Thanks a lot!

  5. Sheila says:

    I am fairly new to excel and was glad you provided this step-by-step. I was able to duplicate this…. thank you so much! I would like to learn Pivot tables…any future training there?

    • admin says:

      Hi Sheila

      Thank you for your comment.

      I don’t have content on pivot tables at present, but will definitely let you know if/when I do.

    • Shagow says:

      tnx a lot for this tnx a lot for this very helpful! Appreciate your help with the below two fold question:a) what if my worksheet names are not as consistent as in your example ? i.e. instead of region 1, 2, 3 I have alphanum codes such as AB1, DB2, CC3 for worksheet names. is there wild card’ variable make excel look in the next sheet irrespective of its name? b) is there a way to tell excel which is the first worksheet to vlookup into and which is the last ?

      • Analyst says:

        Hi Shagow

        a) I don’t think so. The worksheet names won’t matter though.
        b) Ditto. I believe you’d have to do this manually. Would it not be easier to simply dump all the data you want to lookup in one sheet, instead of having to lookup several sheets?

  6. Mike says:

    Very helpful and nicely written. thanks

  7. kaddu says:

    Thank you.
    That was fantastic.
    Any slides on Pivotal table ???

    • admin says:

      Hi Kaddu

      I don’t have slides on pivot tables at the moment, but I am working on another site which will cover other functions in Excel – I will look into covering pivot tables as well, but the site is still work-in-progress and I’m not sure exactly when it will launch. If you have any other questions about vlookups in the meantime, let me know.

  8. Suzie says:

    This is great. Gave perfect step by step instructions for me.
    Only mis wording appears to be
    in STEP 6: “this is because column J is three columns away from column I” I think you mean column J is the 3rd column in the area you are looking up the data for.

    • admin says:

      Thanks Suzie – apologies for the delay – you are correct – I have made a slight amendment – it was a typing error!. I meant to write “this is because column J is three columns away from column H” rather than column I. Thanks for your observation, it is appreciated.

  9. patricia says:

    thank you for dumbing this down with visuals. I was able to create a vlookup along with you and got it!


Leave a Reply

If you wish to receive a response, please ensure that you either tick the "notify me of follow-up comments via e-mail" box below or click on the "subscribe" link (also below). You may also need to check your junk mail folder, depending on your e-mail settings