See how to use the VLOOKUP function when the lookup table is in another workbook. Video shows the steps and written steps are shown too. NOTE: With this technique, the other workbook must be open
If you're filling in an order form in Excel, you can use the VLOOKUP function to find the selling price for each item in the sales order.
In the screen shot below, the order form is on the Order worksheet, and a VLOOKUP formula in column B pulls the cost from a pricing table, named tblProduct, on the Products sheet.
However, you might want to keep your price list separate from the orders workbook, so the prices are easy to update. If your price list is in a different workbook, you can still use a VLOOKUP formula to pull the data, by referring to the external list.
NOTE: With this technique, the other workbook must be open
In the example shown below:
The prices and orders are stored in named Excel tables, and will automatically adjust in size if items are added or removed.
The master price list is on the Prices sheet, in a table named tblPrices
The orders list is on the Orders sheet, in a table named tblPrices
To the VLOOKUP formula, that finds a value in a different workbook, follow these steps.
With this technique, the price list lookup table workbook must be open. .
After creating the formulas, if you close the price list lookup table workbook:
You can see the start of the path, in the screen shot below
However, if the price list lookup workbook is closed, and you enter a new product, or recalculate the Orders workbook, the formulas will show #REF! errors
To avoid those #REF! errors:
To try the VLOOKUP from another workbook, get the two workbooks that are shown in this tutoria. The files are both in a zipped folder -- click here to download the VLOOKUP Other Workbook folder. Both files are in xlsx format, and do not contain any macros..
Last updated: April 12, 2021 7:43 PM