Vlookup between two workbooks / Vlookup between multiple worksheets 

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 this free excel vlookup 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

Before you start, open both workbooks from this site  AND make sure you’ve clicked the “Enable editing” button in BOTH workbooks then save them to your Desktop, otherwise any changes you make won’t work!

screenshot showing 'enable editing' button

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

An image showing a list of foods in column B with unique IDs in column A, but not cost information in col. C

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

An image showing the Most Recently Used functions, with the vlookup at the top.

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.

An image showing All functions in the “insert function” window, with the vlookup at the bottom.

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.

The “functions argument” window with the first lookup value populated – cell A2.

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!

NB – an alternative, but IMPORTANT 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 A3. So you’d highlight cells A3 to C12, because C12 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,$A$3:$C$12,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 a spreadsheet at work  doesn’t allow you to highlight columns A to C 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 below then proceed to Step 6, the second last step.

The “function argument” window with the Table Array populated – columns A to C in sheet 2.

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.

The “function argument” window with the column index number populated – 3 because C is 3 away from A.

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 “10 common problems” page above.

Your formula in the end should look like this (in cell C2 in the workbook without prices): =VLOOKUP(A2,'[Workbook-with-prices.xls]Sheet1′!$A:$C,3,FALSE)

If your formula does not look like this, then you need to check that you’ve followed the steps above correctly.

3 people said they couldn’t get to the second workbook in step 5. This is because they hadn’t clicked on the ‘enable editing’ button shown in the screenshots at the beginning of this tutorial in BOTH workbooks! Please make sure you do this if this problem applied to you, before posting a comment. In fact, you should ALWAYS click ‘enable editing’ if you’re using formulas in Excel. It’s good practise and will help you. 

If you want to learn about Pivot Tables, you can do so via this website we created following user demand: http://pivottablesinexcel.com/

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

Subscribe via RSS

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