VLOOKUP From Another Workbook in Excel

A VLOOKUP from another workbook works exactly like a normal VLOOKUP, except the table argument points at a range in a different file. The reference looks like [Prices.xlsx]Prices!$A$2:$C$6: the file name in square brackets, then the sheet name and an exclamation mark. This guide is for anyone who keeps a price list, staff list or product list in one file and needs to pull values from it into another.

How a reference to another workbook looks#

A reference to another workbook has three parts: the file name in square brackets, the sheet name, then ! and the range. For a price list saved as Prices.xlsx with a sheet called Prices:

[Prices.xlsx]Prices!$A$2:$C$6

Inside VLOOKUP, it takes the place of the table:

=VLOOKUP(B2,[Prices.xlsx]Prices!$A$2:$C$6,3,FALSE)

The rest of the formula is the same as any VLOOKUP. If the arguments are new to you, start with the guide to using VLOOKUP or the VLOOKUP function guide.

How to VLOOKUP from another workbook, step by step#

The easiest way is to let Excel write the reference by clicking. Say Orders.xlsx lists product IDs in column B, and Prices.xlsx has this table on its Prices sheet:

1. Open both workbooks.
2. In Orders.xlsx, click C2 and type =VLOOKUP(B2,
3. Switch to Prices.xlsx. On Windows, use View > Switch Windows or press Ctrl+Tab. On a Mac, pick the file from the Window menu.
4. Click the Prices sheet tab and select A2:C6. Excel adds the file and sheet name for you.
5. Type ,3,FALSE) and press Enter. Excel takes you back to Orders.xlsx.
6. Fill the formula down the column (Windows: Ctrl+D; Mac: ⌘D).

If B2 holds P-104, the result is 1.75. Notice that Excel added the $ signs itself: references you select in another workbook are absolute by default, so the table stays fixed as you fill down. For the column number, count from the first column of the selected table, not from column A of the sheet; the guide to the VLOOKUP column index number shows how.

What the formula looks like when the other file is closed#

When you close Prices.xlsx, Excel rewrites the reference with the full folder path, in single quotes:

=VLOOKUP(B2,'C:\Reports\[Prices.xlsx]Prices'!$A$2:$C$6,3,FALSE)

You don't need to type this yourself. The formula still works while the file is closed: VLOOKUP reads the values that were last saved in Prices.xlsx. On a Mac, or for files in OneDrive or SharePoint, the path looks different (a Mac folder path or a web address), but it works the same way.

Not every function can do this. SUMIF and COUNTIF return #VALUE! when the workbook they point at is closed. VLOOKUP, INDEX, MATCH and XLOOKUP don't have that problem.

File and sheet names with spaces#

If the file or sheet name has spaces or special characters, the workbook-and-sheet part needs single quotes around it:

=VLOOKUP(B2,'[Price List.xlsx]Price 2026'!$A$2:$C$6,3,FALSE)

The opening quote goes before the [ and the closing quote goes before the !. Excel adds them automatically when you select the range with the mouse, which is another reason to click instead of typing. The same rule applies between two sheets in one file; see VLOOKUP between two sheets.

How to update, change or break the link#

A VLOOKUP into another file creates an external link. Excel manages these links in one place: Data > Edit Links, called Data > Workbook Links in Microsoft 365.

• Update the values. When you open a file with links, Excel may show a security bar saying links are disabled. Click Enable Content if you trust the source file, and the lookups read the latest saved values.
• Change Source. If Prices.xlsx was moved or renamed, select the link and choose Change Source, then pick the new file. Every formula updates at once.
• Break Link. This replaces the lookup formulas with their current values. Use it when you send the file to someone who doesn't have the source. You can't undo it, so save a copy of the file first.

Why VLOOKUP from another workbook returns an error#

Errors in a cross-workbook lookup have the same causes as any VLOOKUP, plus a few about the link itself:

1. #REF! or a "Update Values" window. Excel can't find the file or the sheet. The file was moved, renamed or the sheet tab was renamed. Fix it with Change Source.
2. #N/A. The ID isn't in the first column of the table, often because of extra spaces or numbers stored as text. Check that you used FALSE; see VLOOKUP exact match.
3. Old values. The source was changed but not saved, or links weren't updated when you opened the file. Save the source and update the links.
4. The table didn't grow. A fixed range like $A$2:$C$6 doesn't include rows added later. Select a larger range or whole columns ($A:$C), or make the source an Excel table.
5. #VALUE! from other functions. If a SUMIF or COUNTIF next to your lookup points at the closed file, open the source file to fix it.

Should you copy the data in instead?#

If the source rarely changes, copying it in is often simpler than a link. Right-click the Prices sheet tab, choose Move or Copy, pick your workbook and tick Create a copy. Your VLOOKUP then points at a sheet in the same file, with no links to manage.

Keep the link when the source is updated often by someone else and you always need the latest values. In Excel 2021 and Microsoft 365, XLOOKUP works across workbooks too and doesn't need a column number. To bring back several columns at once, see VLOOKUP with multiple columns.

Practise it#

The exercise on this page practises the lookup itself on one sheet, the part you need to get right first. Then practise VLOOKUP exact match in a live spreadsheet, and practise worksheets and cross-sheet references, where you look up prices on another tab and total across monthly sheets. The same skills carry straight over to other workbooks.