XLOOKUP Not Working Across Workbooks? Fix the Links

Yes—XLOOKUP can look up a value in another Excel workbook. For the most reliable results, open both workbooks, check that the external references point to the correct file and ranges, and confirm that the lookup value matches the source data exactly.

How XLOOKUP works across workbooks

XLOOKUP searches one range for a value and returns the corresponding value from another range. Those ranges can be on a different worksheet or in a separate workbook. The main difference is that a cross-workbook formula depends on an external link: Excel must be able to locate the source file and read the referenced cells.

For example, suppose your active workbook has a product code in A2. A separate workbook named PriceList.xlsx has product codes in column A and prices in column C on a worksheet named Products. With both files open, a formula can look like this:

With the product code to find in the current workbook’s A2, and product codes in PriceList.xlsx, Products!A2:A500, with prices in Products!C2:C500, use =XLOOKUP(A2,'[PriceList.xlsx]Products'!$A$2:$A$500,'[PriceList.xlsx]Products'!$C$2:$C$500,"Not found"). If A2 contains B-104 and that code’s price in the source file is 12.5, the formula returns 12.5.

The workbook and worksheet names inside the references identify where XLOOKUP should search and which values it should return. In practice, it is safer to build the formula by selecting the source ranges with the mouse while both files are open. Excel will create the external references and may include the source file’s folder path automatically.

Keep the source workbook open while troubleshooting

Open the workbook containing the lookup formula and the workbook containing the source data in the same Excel session. XLOOKUP references to another workbook can be unreliable when that source file is closed; depending on the reference and Excel version, a closed source may produce a #REF! error. Opening both files is a useful first check, even if the formula previously appeared to work.

If you need a formula to work consistently without the source workbook open, test that requirement with your exact Excel version and file setup before relying on it. A formula that works while both files are open is not proof that the closed-file workflow will work. For repeatable reporting, consider storing the source table in the same workbook, or using Power Query to import and refresh the source data.

Also check that the source file has not been renamed, moved, or saved into a different folder. Links may still point to an old location after a file is moved. If Excel asks you to locate the source, select the intended workbook rather than dismissing the prompt and assuming the formula has been repaired.

Check the external reference and its ranges

Look at the formula in the formula bar and verify each part of the reference. The lookup range must contain the values you want to match; the return range must contain the results you want back. The ranges must also have compatible sizes. If the lookup range covers rows 2 through 500 but the return range covers rows 2 through 400, the formula is not set up correctly.

Confirm the file name: Make sure the reference names the actual source workbook, including its extension.
Confirm the worksheet: Check that the worksheet name is still correct. A renamed or deleted sheet can break a link.
Confirm the columns: Ensure the lookup range is the column containing the key, and the return range contains the desired result.
Confirm the row limits: Include every relevant source row, including new records added since the formula was first made.
Check absolute references: Dollar signs, as in $A$2:$A$500, keep ranges fixed when you copy a formula down.

When a workbook or worksheet name contains spaces, Excel uses apostrophes around the external reference, as in the example. Avoid trying to reconstruct a long file path by hand if Excel can create it for you: open both workbooks, start the formula, and select the cells in the source workbook.

Diagnose the error you see

The displayed error often narrows down the cause. Fix the reference or data issue before changing the formula’s matching behavior.

#REF!: Excel cannot resolve a reference. Open the source workbook, check whether it was moved or renamed, and verify the worksheet and ranges still exist.
#N/A: An exact match was not found. Check the lookup value, source data, spaces, and data types. If you supplied an if-not-found message, such as Not found, XLOOKUP may display that message instead of #N/A.
#VALUE!: Inspect the formula arguments and the dimensions of the referenced ranges. Make sure the formula has not been damaged by editing or copying.
#NAME?: Excel may not recognize XLOOKUP, often because the file is being opened in an older Excel version that does not support it.
#SPILL!: The formula is trying to return multiple results, but cells in the spill area are blocked. Clear the obstructing cells or use a formula setup that returns one result.

A result that looks wrong without showing an error can be harder to spot. Check that the return range is the intended column and that the lookup value is unique if you expect one specific answer. By default, XLOOKUP returns the first match it finds, searching from the beginning of the lookup range.

Check for hidden differences in the lookup values

Two entries can look identical on screen but fail to match. A product code stored as text may differ from the same-looking code stored as a number. Leading or trailing spaces, nonbreaking spaces copied from a web page, and inconsistent punctuation can also prevent a match. Compare the actual cells in both workbooks and, if needed, clean or standardize the source data before relying on the lookup.

Take care with identifiers that contain leading zeroes. For example, the text code 00427 is not necessarily equivalent to the number 427. If those zeroes are meaningful, store the identifiers consistently as text in both files rather than converting them to numbers. Dates can cause a similar problem when one workbook contains real Excel dates and another contains date-looking text.

XLOOKUP uses exact match by default, which is generally appropriate for IDs, names, and product codes. Do not switch to approximate matching just to make a failed lookup return something: approximate matching can produce a plausible but incorrect result when the lookup range is not prepared for that method. If the value should be a literal text match containing wildcard characters such as an asterisk, XLOOKUP’s match mode can be set for wildcard matching, but ordinary exact matching is the safer starting point.

Check Excel version and calculation settings

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It is not available in Excel 2019 or Excel 2016. If you send a workbook containing XLOOKUP to someone using an unsupported version, they may see an unrecognized-function error even though the formula works on your computer. Ask what version they use before assuming the workbook link itself is broken.

In Excel for Microsoft 365 on Windows, check Formulas > Calculation Options and confirm that calculation is set to Automatic if results are not updating. With both workbooks open, edit the lookup value or formula and see whether the result recalculates. Excel for Mac has XLOOKUP in supported Microsoft 365 and Excel 2021-or-later releases as well; menu details can differ by release. Excel for the web can also work with supported formulas, but access to the source workbook and its link location still matters.

When sharing a workbook, remember that the recipient may not have access to your local folder or network drive. A reference to a file on your computer does not automatically grant another person access. Store linked files in a location both users can reach, and verify the link from the recipient’s environment if the workbook is important.

Choose a maintainable setup

A formula linked to another file can be useful for a small, stable lookup table. It is less convenient when the source is frequently moved, renamed, or distributed among people with different file access. Decide whether a direct link is the right arrangement for how the workbook will actually be used.

Use a direct XLOOKUP link when the source workbook is stable, users can access it, and you need a straightforward lookup.
Bring the data into the same workbook when portability and fewer external dependencies matter more than maintaining a separate source file.
Use Power Query when you need to import and refresh a larger source table or repeat the same data-preparation steps. It imports data; it is not a replacement lookup formula in every workflow.
Document the source by noting the expected file name, folder, and worksheet so that another person can restore a broken link.

For large data sets or many formulas, limit references to the rows and columns you need rather than pointing every lookup at entire columns in an external workbook. Clear range boundaries can make formulas easier to inspect and may avoid unnecessary work. If you extend the source table later, update the referenced range or redesign the setup so new records are included.

How XLOOKUP compares with older lookup formulas

XLOOKUP can search in either direction and keeps the lookup and return ranges as separate arguments. That makes it more flexible than VLOOKUP, which normally searches the leftmost column of its table and returns a column to its right by column number. However, changing to XLOOKUP does not remove the need for a valid external link: the file, sheet, ranges, and data still have to be accessible and correct.

INDEX and MATCH can be an option for people who need compatibility with older Excel releases, but it is a different formula setup and also depends on correct references. If someone using Excel 2016 or 2019 must open the workbook, consider compatibility before choosing XLOOKUP. Do not replace a working formula solely because another function is newer; choose based on the recipients’ Excel versions and the workbook’s maintenance needs.

Frequently Asked Questions

Does the other workbook need to be open for XLOOKUP?
Keep both workbooks open while setting up and troubleshooting the formula. XLOOKUP links to a closed workbook can return #REF! in some situations, so test your specific setup rather than assuming a closed-source link will remain reliable.

Why does XLOOKUP return #N/A when the value is in the other workbook?
The visible values may differ because of spaces, text-versus-number formatting, leading zeroes, or punctuation. Also confirm that the formula searches the correct source column and that the referenced range includes the matching row.

Can XLOOKUP return more than one result from another workbook?
XLOOKUP can return an array when its return range includes multiple columns or when the lookup value produces multiple returned cells. The results need clear cells to spill into. Cross-workbook array behavior can be more sensitive to whether the source workbook is open, so test it with both files open.

Why does the formula work for me but not for a colleague?
Your colleague may lack access to the source file, may have a different file path, or may be using Excel 2019 or 2016, which do not support XLOOKUP. Check both their Excel version and their access to the linked workbook.

Can I use XLOOKUP across worksheets in the same workbook instead?
Yes. A reference to another worksheet in the same workbook does not rely on an external file link. The lookup and return ranges still need to point to the correct worksheet and corresponding rows.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.