In a VLOOKUP formula, you can refer to a lookup table that is in a different Excel workbook. In this example, the price list is in one workbook, and the VLOOKUP formula is in the order data entry workbook.
It is easier to create the reference if both Excel workbooks are open when you create the VLOOKUP formula.
After you create the VLOOKUP formula, the price list workbook can be open or closed, and the formula will continue to work correctly.
See the instructions on my Contextures blog:
And there are more VLOOKUP examples on my website:
http://www.contextures.com/xlFunctions02.html
Instructor: Debra Dalgleish, Contextures Inc.
Get Debra’s weekly Excel tips: http://www.contextures.com/signup01
More Excel Tips and Tutorials: http://www.contextures.com/tiptech.html
Subscribe to Contextures YouTube: https://www.youtube.com/user/contextures?sub_confirmation=1
VIDEO TRANSCRIPT
In Excel you can use the VLOOKUP formula to pull data from a table.
In this example, we have orders, and we’d like to automatically show the cost for each product that’s sold.
Here we’re selling paper and staplers and pens. We have a price list in another workbook, and here you can see each item and the cost that we sell it for. The two are in different workbooks.
Usually, if you’re using VLOOKUP, you’d use a list that’s in the same workbook, but in this case we’re going to refer to a different workbook. To make this easy, I’ve got both the workbooks open and I’m going to show them side by side.
On the Ribbon, on the View tab, I’ll click Arrange All, and I’d like to see them vertically arranged. I’ll click OK, and now I can see the price list and beside that is the list of orders.
For the VLOOKUP, I will start with an equal sign and type VLOOKUP and an open bracket. Then I click on the cell that has the value I want to look for in the price list.
I want to find the price of paper, so I’ll click on that, and then type a comma, to start the next argument. The next argument is where is the lookup table?
This is in the other workbook, so I’ll click on that and select the cells where the price list is. You can see that in the other workbook now, and I’ll press the comma to start the next argument.
And to finish, I want to say which column the prices are in. We can see that they’re in 1, 2. So the prices are in column 2.
I’ll type 2 and then I want an exact match always. I don’t want just some random price that seems close. So I’ll click False, close the bracket and press Enter.
Now, in the formula bar, you can see there’s the name of the other workbook and the sheet and the cells where the price list is. I’ll just copy that down to the rest of the orders and everything’s working nicely.
If I go back and close the price list, save the changes. Now this workbook, we can see the VLOOKUP shows the full path to that other workbook, but everything still works well.
The VLOOKUP works, even though the workbook with the price list is closed now. So you can use VLOOKUP to refer to a list in another workbook, and it’s easiest if you make the link with both workbooks open.