Home > Formulas > VLOOKUP > Other File VLOOKUP From Other Workbook 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

Introduction

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.

Lookup in Another Workbook

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:

• lookup table is in the PriceList.xlsx workbook, on the Prices sheet
• orders are in the PriceLU.xlsx workbook, on the Orders sheet

Workbook Setup

The prices and orders are stored in named Excel tables, and will automatically adjust in size if items are added or removed.

In the PriceList workbook:

The master price list is on the Prices sheet, in a table named tblPrices

• The column with product names is a named range - Product
• The table's data range (A4:B7) is named PriceLU. That will be used as the lookup range

In the OrderPriceLU workbook:

The orders list is on the Orders sheet, in a table named tblPrices

• There are no named range in this workbook

Create VLOOKUP Formula

To the VLOOKUP formula, that finds a value in a different workbook, follow these steps.

1. Open both workbooks (this step isn't required, but makes it easier to create the link)
2. In the Orders tablein cell D4, start the VLOOKUP formula, with:
• =VLOOKUP(
3. The 1st argument is the value to find, so type B4, where the product name is:

• =VLOOKUP(B4
1. Type a comma, to end the 1st argument
2. The 2nd argument is the lookup range - select the product names and prices in the master workbook.
3. Excel automatically adds the other workbook's name, and lookup range name, in the formula.
• =VLOOKUP(B4,PriceList.xlsx!PriceLU
4. Type a comma, to end the 2nd argument
5. Next, type a 2, which is the column number in the lookup range, which you want to retuen. That's were the prices are stored, in the PriceLU range.
• =VLOOKUP(B4,PriceList.xlsx!PriceLU,2
6. Type a comma, to end the 3rd argument
7. We want an exact match for the prices, so type FALSE, in the 4th argument
• =VLOOKUP(B4,PriceList.xlsx!PriceLU,2,FALSE
8. Then, type a closing bracket, and press Enter, to complete the formula

Close the Lookup Workbook

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:

• the VLOOKUP formula will still show the correct prices
• The full file path for the price list lookup table workbook will be shown in the formula.

You can see the start of the path, in the screen shot below

• 

Closed Workbook - #REF! Errors

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:

• Open the the price list lookup workbook first
• Then open the Orders workbook
• Keep the price list lookup workbook open while working in the Orders workbook
• When finished, save and close the Orders workbook first
• Then close the Price list workbook

Download the Sample File

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

More Tutorials

VLOOKUP

VLOOKUP Troubleshooting

INDEX / MATCH