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

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)

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 3^{rd} 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:**

Simple explanation, thank you

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

You’re most welcome.

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

You’re welcome.

THNX

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

You’re welcome.

Very useful, thanks a lot

Great job.Thanks.Very well done.

Thanks,its really educative

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?

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

Really Useful

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.

You’re welcome. Good luck with the interview!

I’m sure you’ll pass!

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

Thanks

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

Thanks a million for your tutorial.

Hi Fel

There is another example with two workbooks here: http://howtovlookupinexcel.com/vlookup-between-two-workbooks

thanx a lot buddy

Outstanding and Perfect

Thanks

Thank you.

PRECISE AND EFFECTIVE. BRILLIANT !!

Thanks!

Thanks a lot

its very awesome

Thank you.

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

Thanks – will let you know if/when I’ve got something on the hlookup.

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

Hi Mike

Yes, solution number 6 on this page will do it – it returns a “0″ instead of an N/A.

http://howtovlookupinexcel.com/7-common-problems-with-vlookups

If you send me the formula you used, eg (VLOOKUP(A2,I:M,5,FALSE), I’ll amend it so it’s in the format in solution 6 and send it back to you.

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

many thanks

Mike

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

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.

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

You’re welcome.

Dear Admin,

Thank you so much for your useful tutorial.

You’re welcome.

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

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

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.

D’oh! Use that all the time but just didn’t think of it. Thank you.

Thanks for giving such a friendly example.

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

You’re welcome.

It’s so useful for beginners.nice

THIS IS WONDERFUL . THANKU SO MUCH.

Thank You….

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

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