Do you want to be able to go home earlier?

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.And vlookup in Excel 2007/2010 is very powerful

This is a step by step vlookup tutorial (only 7 steps) suitable for beginners and 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, and Excel 2010 (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 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.

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!

(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 Category to all, select ‘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 –

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

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

We 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

The post How to vlookup in Excel 2007/2010 appeared first on How to vlookup in Excel.

Below is an example of a vlookup between two different workbooks, which a lot of you have been asking about.

To make it as easy as possible for you to understand the steps, we’ve included the two files we use for the tutorial here: Workbook without prices and Workbook with prices.

You can’t learn how to drive a car by just reading a book, and for that reason, we’ve given you access to the files that we use – after all, the best way to learn is by doing! Other sites do not offer you the files, but we do. We recommend that you have both files open before you begin the tutorial.

The workbooks contain a list of products which the majority of people would buy in a supermarket – Milk, Bread, Jam, etc.

We have intentionally kept the files simple. We’re going to use the vlookup function to get the prices of the food data from the “workbook with prices” into the “workbook without prices”.

Step 1

When you open the “workbook without prices” file you will see the screen below. Column C in the “sheet without prices” in this workbook is where we will pull in the “Price of the Goods” from the other workbook. Cell C2 will already be selected – if it isn’t, click in cell C2, as that’s where we want the first result to appear.

Step 2

Click on the “fx” button which is just above column B (see screenshot below). The ‘Insert Function’ window will then show up (also shown in the screenshot below). If the “vlookup function” is already selected, like in the screenshot below, click “ok”.

If the “vlookup function isn’t already selected, then in the “or select a category” field shown in the screenshot above, change the option in the drop-down from “most recently used” to “All” then scroll down until you get to the vlookup function, as shown in the screenshot below.

Once you’ve selected the “vlookup function” from the drop down menu and clicked ok, the “Function Arguments” window will appear.

Step 3

The “Function Arguments” window will show 4 different fields which need to be populated for the vlookup to work. For the first field, the “Lookup_value”, click on cell A2, as illustrated in the image below.

Step 4

Now click into the “Table_array” field (a “table array” is a fancy phrase for describing two or more columns of data).

Step 5

This part is where the other workbook comes in! After you’ve clicked in the “Table_array” field, click into the other workbook (it’s called “workbook with prices”) and highlight columns A – C in that workbook. We highlight columns A – C in this workbook because column A has the “Lookup_value” that is in the “workbook without prices” and column C has the “Price of Goods” data that we want to extract from this workbook. When you highlighted columns A – C, you may have noticed that the characters “3C” appeared at the top of column D – this is a quick way of Excel telling you how many columns there are in the data you’ve highlighted – it saves you having to count the columns manually, which a lot of people in offices do! If you didn’t notice that, clear the data in the “table array” field and highlight columns A – C again in the “workbook with prices” – ensure you hold the mouse key as as soon as you let go, the “3C” will disappear. Many office workers don’t know this trick and waste a lot of time manually counting the columns! But we’re here to save you time!

Step 6

Click into the “Col_index_num” field – you will notice that your screen returns back to the first file, the “workbook without prices” – don’t panic! All you have to do here is put in the number 3. We put in the number 3 here because column C in the “workbook with prices” has the prices of the goods that we want and it is three columns away from column A, which has the unique lookup values that we are using. Or if you noticed the “3C”, it means “3 columns” so you know you have to insert the number 3 in the “Col_index_num” field. A screenshot of how everything should look so far is below.

Step 7

The final step! Click into the ‘range lookup field’ and type the word ‘FALSE’. Click ‘OK’ and, like magic, the formula will return the first result that we’re looking up. After that, simply drag down the formula in cell C2 in the “workbook without prices” and the “prices” of all the other goods in this file will auto-populate. Easy, right!? Typing the word ‘FALSE’ here will ensure that the vlookup only returns an EXACT match. If it doesn’t find an exact match for the ‘lookup value’ we’re using, then it will return an N/A – more about this in the “6 common problems” page above.

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

We 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

The post Vlookup Between Two Workbooks appeared first on How to vlookup in Excel.

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! And yet vlookup in Excel 2003 can be so handy!

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, and Excel 2010 (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.

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)

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.

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 –

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

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

We 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

The post How to do a vlookup in Excel 2003 appeared first on How to vlookup in Excel.