EXAMPLE 9: VLOOKUP from another Workbook
It is very common to perform VLOOKUP from another worksheet within the same workbook.
Yet, it is less common to perform VLOOKUP from another workbook.
The potential problem with this type of integration is that there’s no guarantee that the referenced workbook will always be available.
If you rename the referenced workbook, or move it to a different location, the link breaks.
However, Excel being so smart, doesn’t completely leave you stranded in this kind of situation. Instead, it continues to work with the most recent version of the data it was able to retrieve before the link broke. Lucky you!
In this example, you’ll learn how to write the VLOOKUP formula to retrieve a value from a different workbook.
Just like in the previous example, it is not always you’ll have your VLOOKUP table stationed in the same workbook.
Sometimes the table through which you’ll look and retrieve values will be in another workbook.
The concept to use here is almost the same as in the previous example.
Thus, to perform VLOOKUP from another workbook, when specifying the [table_array] argument, put the workbook name at the beginning of the reference, and then enclose it with square brackets. Then follow the file name with the sheet name, an exclamation mark, and then the range address or name.
If you really want to learn how this works, download the example file and create a new Excel workbook on your desktop.
Don’t change the default file name and worksheet name – leave it as Book1 (for the file name) and sheet1 (for the sheet name).
If you are using a different workbook for this example, then rename the file as Book1 and make sure it has a worksheet named sheet1.
If you are comfortable with this stuff and you know you won’t get confused, you can come along with your own workbook and worksheet names. It’ll work well provided you apply the necessary techniques.
Now that you have all this set, open the newly created workbook and in the sheet1 worksheet, create a product catalog like in the previous example.
You can also copy and past the table if you wish. However, ensure that you start pasting or creating your table from cell A1.
Now switch back to the example 9 worksheet, in the price column, type the formula below:
The C17 in the formula is the lookup value which is referring to the content in Cell C7. Input any product name in this cell or you will get an error.
This VLOOKUP formula will retrieve the product price from the workbook called Book1 on your desktop.
Thus, if you want to VLOOKUP from another workbook, all you really need to do is write the workbook name in square brackets, and follow it with the sheet name, an exclamation mark and then the range.
However, if you don’t want to manually enter the [table_array] argument, there’s a simple technique to have Excel insert it for you.
First, open the workbook from which you want to reference. In our case, Book1 on the desktop.
Next, in the process of writing the formula, when you are about to specify the [table_array] argument, use Alt+Tab to navigate to Book1, then select the product catalog table or range.
When you do this look in the address bar and you’ll see that Excel has done the referencing for you, now provide the remaining arguments and press enter to execute the function.