Can XLOOKUP look up data in another workbook?

Yes. Select the ranges in the other workbook while writing the formula; Excel adds the workbook reference.

Does XLOOKUP work if the other workbook is closed?

Yes, it returns the values saved in the link cache. Update links to refresh them. SUMIF and COUNTIF don't work with closed workbooks.

How do I fix broken links to another workbook?

Open Data → Edit Links (Workbook Links in Microsoft 365) and use Change Source to point to the file's new location.

Why does XLOOKUP return #N/A for a code that exists in the other workbook?

The lookup value and the lookup column differ in type or spacing, such as the number 1001 against the text "1001", or a trailing space. TRIM the value and make both columns the same type.

Can I use XLOOKUP across workbooks in Excel for the web?

Yes, when both files are in OneDrive or SharePoint. The reference uses the file's URL, and the link updates when the files are opened.

How to use XLOOKUP with another workbook

By the Analistable team · Updated 2026-10-07 · 5 min read

Open both workbooks, start typing =XLOOKUP(A2, and click into the other workbook to select the lookup and return columns. Excel writes the reference for you, such as [Prices.xlsx]Sheet1!$A:$A. When the source is closed, the path is added automatically and results come from the last saved values until you refresh links under Data → Edit Links (Workbook Links in Microsoft 365).

Part of our guide: How to join spreadsheets on a common column

The syntax

=XLOOKUP(A2, [Prices.xlsx]Sheet1!$A:$A, [Prices.xlsx]Sheet1!$C:$C, "Not found")

With Prices.xlsx closed, Excel shows the full path:
=XLOOKUP(A2, 'C:\Data\[Prices.xlsx]Sheet1'!$A:$A, 'C:\Data\[Prices.xlsx]Sheet1'!$C:$C, "Not found")

Selecting the ranges by clicking is more reliable than typing paths, especially for files on OneDrive or SharePoint, whose references are URLs.

Simple links between workbooks

To show one cell from another file, type = and click the cell in the other workbook: ='[Budget.xlsx]Summary'!$B$4. The value updates when the source changes and both files are open, or when you update links.

Closed workbooks: what works

Functions with references to a closed workbook
Function	Works with the source closed?
XLOOKUP, VLOOKUP, INDEX/MATCH	Yes — uses the values saved in the link cache
Direct references (='[Book]Sheet'!A1)	Yes
SUMIF, COUNTIF, SUMIFS, COUNTIFS	No — return #VALUE! until the source is opened
INDIRECT	No — returns #REF!

If you need conditional sums from a closed file, use SUMPRODUCT instead of SUMIFS, or load the source with Power Query.

Managing links

Update values: Data → Edit Links (or Workbook Links in Microsoft 365) → Update Values.
Source moved or renamed: Change Source and pick the new file.
Security prompt on opening: Excel asks before updating external links. Choose Update if you trust the source.
Remove links: Break Link converts formulas to their current values — keep a copy first.

When to stop linking files

A chain of linked workbooks is hard to audit and breaks when files move. If you're matching whole tables rather than a few values, import the other workbook with Power Query and merge — see joining two tables in Excel — or compare the lookup options in VLOOKUP vs XLOOKUP vs INDEX MATCH.

XLOOKUP needs Microsoft 365, Excel 2021 or later; colleagues on older versions see #NAME?. INDEX/MATCH works everywhere.

Files on OneDrive and SharePoint

When both workbooks are in OneDrive or SharePoint, references use URLs instead of drive paths and update when both files are opened in Excel for the web or desktop. Moving either file breaks the link just as it does on a local drive.

Worked example: prices from a supplier file

Orders.xlsx lists SKUs in column A. Prices.xlsx, kept by purchasing, has SKU in A and Unit price in C. In Orders.xlsx, C2 (column B holds the quantity):

=XLOOKUP(TRIM(A2), [Prices.xlsx]Sheet1!$A:$A, [Prices.xlsx]Sheet1!$C:$C, "Not found")

Orders.xlsx after the lookup
SKU	Qty	Unit price	Line total
D-100	4	85	340
C-200	10	22.5	225
L-300	2	40	80
X-999	1	Not found

The three found lines total 340 + 225 + 80 = 645. X-999 isn't in the price file, so it shows the fallback text instead of #N/A, and the line total is left blank with =IF(ISNUMBER(C5), B5*C5, ""). Counting the fallback with =COUNTIF(C:C, "Not found") gives a quick measure of how complete the price file is.

Two-way and multi-column lookups across files

Return several columns at once: =XLOOKUP(A2, [Prices.xlsx]Sheet1!$A:$A, [Prices.xlsx]Sheet1!$B:$D) spills three columns in Microsoft 365.
Match on two criteria (SKU and region): =XLOOKUP(A2&"|"&B2, [Prices.xlsx]Sheet1!$A:$A&"|"&[Prices.xlsx]Sheet1!$B:$B, [Prices.xlsx]Sheet1!$C:$C). Keep the ranges bounded (A2:A5000) for speed, and expect array formulas like this to need the source open to recalculate.
Approximate match for price bands: set match_mode to -1 (exact or next smaller) with the band starts sorted ascending.

Troubleshooting

Symptom, cause and fix
Symptom	Cause	Fix
#N/A for codes that exist	Text vs number keys, or extra spaces	TRIM the lookup value; make both columns the same type
#REF! after opening	The source was renamed or moved	Workbook Links → Change Source
Old values shown	Links were not updated when the file opened	Data → Workbook Links / Edit Links → Update Values
#NAME? on a colleague's PC	Their Excel predates XLOOKUP	Use INDEX/MATCH, or have them open it in Microsoft 365 or Excel for the web
Very slow workbook	Whole-column external references in thousands of rows	Bound the ranges, or import the source with Power Query

Checking and maintaining links

List every external source: Data → Workbook Links (Microsoft 365) or Edit Links (older versions) shows each file and its status.
Use Find (Ctrl+F) for [ with “Look in: Formulas” to find each cell that refers to another workbook.
Keep linked files in a stable shared folder, and avoid renaming them once other files point at them.
Before sending the file to someone outside the organisation, decide whether to break the links so they see values rather than a prompt about a path they can't reach.

On a Mac, Excel for Microsoft 365 supports XLOOKUP and external references the same way; the stored path simply follows the Mac folder structure. In Google Sheets, pull the other file in with IMPORTRANGE and run XLOOKUP against that range, as described in linking two Google Sheets.

INDEX/MATCH and VLOOKUP versions for older Excel

=IFERROR(INDEX([Prices.xlsx]Sheet1!$C$2:$C$5000,
        MATCH(TRIM(A2), [Prices.xlsx]Sheet1!$A$2:$A$5000, 0)), "Not found")

=IFERROR(VLOOKUP(TRIM(A2), [Prices.xlsx]Sheet1!$A$2:$C$5000, 3, FALSE), "Not found")

Both work in every Excel version and, like XLOOKUP, return cached values when Prices.xlsx is closed. The VLOOKUP version breaks if someone inserts a column in the price file, because the column number 3 doesn't move; INDEX/MATCH and XLOOKUP refer to the return column directly.

Frequently asked questions

Can XLOOKUP look up data in another workbook?: Yes. Select the ranges in the other workbook while writing the formula; Excel adds the workbook reference.
Does XLOOKUP work if the other workbook is closed?: Yes, it returns the values saved in the link cache. Update links to refresh them. SUMIF and COUNTIF don't work with closed workbooks.
How do I fix broken links to another workbook?: Open Data → Edit Links (Workbook Links in Microsoft 365) and use Change Source to point to the file's new location.
Why does XLOOKUP return #N/A for a code that exists in the other workbook?: The lookup value and the lookup column differ in type or spacing, such as the number 1001 against the text "1001", or a trailing space. TRIM the value and make both columns the same type.
Can I use XLOOKUP across workbooks in Excel for the web?: Yes, when both files are in OneDrive or SharePoint. The reference uses the file's URL, and the link updates when the files are opened.