VLOOKUP Not Working Between Workbooks? How to Fix It

When VLOOKUP fails between Excel workbooks, check the external reference, use an exact match, and confirm that the lookup value is in the first column of the selected range. In Microsoft 365 for Windows, a typical formula is =VLOOKUP(A2,'[PriceList.xlsx]Sheet1'!$A$2:$B$100,2,FALSE); the checks below help identify why it returns an error or the wrong result.

How VLOOKUP works across workbooks

VLOOKUP searches vertically down the first column of a range and returns a value from another column on the same row. The source range can be on a different worksheet or in a different workbook, but the basic rules do not change: the lookup column must be the leftmost column in the selected range, and the return-column number is counted from the left edge of that range.

In the example formula, A2 contains a product code in the workbook where the formula is entered. PriceList.xlsx contains product codes in column A and prices in column B on Sheet1. The range begins at A, so 2 tells Excel to return the value in the second column, B. FALSE asks for an exact match, which is usually appropriate for codes, names, and IDs. The source and formula workbooks must be accessible for Excel to follow the link.

VLOOKUP does not search to the left. If the value you want to find is in column B but the result is in column A, selecting B:A will not make VLOOKUP return the left-hand value. You would need a different lookup approach or a rearranged source range.

Check the external workbook reference

A reference to another workbook includes its file name and worksheet name. If the source workbook is open, Excel commonly shows a shorter reference such as '[PriceList.xlsx]Sheet1'!$A$2:$B$100. If the source file is closed, Excel may include its folder path as well. Spaces in a workbook or sheet name generally mean the reference needs single quotation marks around the workbook-and-sheet portion.

To avoid mistyping punctuation, open both workbooks, start editing the formula, and select the source range with the mouse. Excel can insert the external reference for you. Then check that the reference points to the intended file, sheet, and rows. Two files with similar names, or two sheets with similar names, are easy to confuse.

For example, suppose the current workbook has product code P-104 in A2, and PriceList.xlsx has P-104 in A12 and its price of 12.5 in B12. With that layout, the formula below returns 12.5:

With the lookup value in the current workbook’s A2 and product codes and prices in PriceList.xlsx, Sheet1, columns A and B, rows 2:100, use:

=VLOOKUP(A2,'[PriceList.xlsx]Sheet1'!$A$2:$B$100,2,FALSE)

If the files are in different folders, Excel may write a full path, for example 'C:\Reports\[PriceList.xlsx]Sheet1'!$A$2:$B$100. Do not replace a working path with a guessed one: the location must match the file’s actual location. When the source is moved, renamed, or deleted, the link can stop resolving even though the formula still looks structurally correct.

Use an exact match for codes and names

The final argument controls how VLOOKUP matches. FALSE requests an exact match; TRUE requests an approximate match. If you omit the final argument, Excel uses approximate matching, which can produce a plausible but incorrect answer when the first column is not sorted as required. For most cross-workbook lookups involving product codes, employee IDs, postal codes, or names, explicitly use FALSE.

Approximate matching has legitimate uses, such as assigning a rate from a sorted set of thresholds. It is not a safe default for a list of unique identifiers. Sorting a source column does not make approximate matching a substitute for exact matching: if the lookup value falls between entries, Excel can return a different row than you intended.

FALSE does not mean that Excel will treat visually similar values as identical. A number stored as text, an extra space, or a different character can still prevent a match. Exact-match settings tell VLOOKUP what kind of comparison to perform; they do not repair inconsistent source data.

Diagnose the error or incorrect result

The error message often narrows down the cause. Check the formula and the source range before rebuilding the lookup from scratch:

#N/A: Excel did not find an exact match, or the value differs in an important way. Confirm that the lookup value exists in the source column and check spaces, data types, and punctuation.
#REF!: A reference may point to a deleted worksheet, moved or unavailable workbook, or invalid range. Re-select the source range and check whether the linked file still exists at the expected location.
#VALUE!: The column number may be invalid, such as 0 or a number greater than the width of the selected range. If the range has two columns, the return-column number must be 1 or 2.
#NAME?: Check the function spelling and the external reference’s names and punctuation. A malformed formula can make Excel interpret part of the reference as an unknown name.
A result appears, but it is wrong: Check for an omitted FALSE argument, a range that starts in the wrong column, or a column number that points to the wrong return field.

Also verify that the source range includes every relevant row. A reference ending at row 100 will not find a record added in row 101. Fixed references such as $A$2:$B$100 help prevent the range shifting when you copy a formula, but they do not automatically expand to include later rows.

Check data types, spaces, and characters

Two cells can look alike and still contain different values. A product code entered as the number 123 in one workbook and the text value 123 in the other may fail to match. Leading zeros are another clue: the text code 00123 is not the same identifier as the number 123. Decide which format is correct for the data, then make the lookup column and lookup value consistent. Avoid converting identifiers to numbers if leading zeros are meaningful.

Spaces are a frequent source of surprises, especially when data has been copied from a website or exported from another system. A trailing space after a code is easy to miss, and some imported text contains nonbreaking spaces that ordinary cleanup steps may not remove. Compare the values in the formula bar, or inspect the source cells directly. If you clean the data, preserve an untouched copy of the original and check that the cleanup does not change legitimate codes.

Look for differences such as hyphens versus en dashes, apostrophes, or characters from another alphabet that resemble ordinary Latin letters. VLOOKUP is not case-sensitive, so capitalization alone usually does not explain a failed match. It does, however, distinguish other characters and spaces that may look nearly identical on screen.

Work through a reliable troubleshooting sequence

Try these checks in order so you can isolate whether the problem is the formula, the link, or the underlying data:

Confirm that both files are the ones you intend to use and that the source workbook has not been renamed or moved.
Open the source workbook and verify the worksheet, the lookup column, and the row containing the expected record.
Check that the formula’s lookup value is in the first column of its selected range, and that the return-column number points to the desired result.
Add FALSE as the final argument if the lookup requires an exact match.
Compare the lookup value with the source value for text-versus-number differences, hidden spaces, leading zeros, and punctuation.
Test the formula with a value you can locate manually in the source, then check whether the returned value matches that row.

On Windows, Excel for Microsoft 365 can show external workbook links through Data > Workbook Links when that command is available for the workbook. Use the link-management options to inspect or update a source location rather than editing a long path by guesswork. Menu availability and labels can differ in other Excel releases. On Mac, use the workbook’s link controls where available and the file-selection dialog to locate a moved source; file paths differ from Windows paths. Excel for the web may not offer the same link-management controls as the desktop app, so use desktop Excel if you need to repair or manage a complicated external link.

Understand file paths and link updates

Excel can preserve a link to a source workbook even when that source is closed, but the link still depends on finding the file. A workbook saved on a local drive may refer to a different location than one stored on a shared drive or cloud service. Moving a workbook to another computer, changing a folder name, or reorganizing a shared directory can make the reference unavailable.

If Excel asks whether to update links when opening a file, consider whether you trust the workbook and recognize the source before allowing updates. Updating a link can retrieve current data from the external file; declining may leave displayed values based on information last saved in the workbook. If a result appears stale, open the source and check the link rather than assuming the formula has recalculated against current data.

When a shared workbook is used by a team, agree on a stable storage location and avoid casually renaming or moving the source file. Keep a copy of the original before making widespread changes to links. This is especially useful when many formulas depend on the same workbook, because changing one visible formula may not repair every link in the file.

When to use another lookup method

If you have Microsoft 365 or Excel 2021 or later, XLOOKUP can be an option for new formulas. It can search in one range and return a result from another, including a column to the left, so it avoids VLOOKUP’s leftmost-column and numeric column-index constraints. It does not eliminate the need for a valid external workbook link or consistent data. If a file must be opened in an older Excel version, check compatibility before replacing a VLOOKUP that other users rely on.

For an existing workbook, fixing the VLOOKUP may be simpler and less disruptive than replacing it. Consider changing methods when the source layout is likely to change, when you need to look left, or when the formula’s column number is hard to maintain. Whichever method you choose, keep the lookup key unique if each key is supposed to identify one record; VLOOKUP returns the first matching row it encounters, not every duplicate.

Frequently Asked Questions

Why does VLOOKUP work when both workbooks are open but fail when one is closed?
Excel may be relying on a link to a file path that is no longer valid or available. Check whether the source workbook was moved or renamed, then use Excel’s link controls or select the source file again to repair the reference.

Why does VLOOKUP return #N/A when the value is visibly in the other workbook?
The cells may differ in data type, contain an unnoticed space, or use different characters. Confirm that the source value is in the first column of the range, and compare the underlying values rather than relying only on how they look.

Does VLOOKUP require the source workbook to stay open?
No. Excel can use external workbook references when the source file is closed, provided the link still points to an accessible file. A closed-file reference may include the full file path.

Why does VLOOKUP return the wrong row instead of #N/A?
Check whether the formula uses TRUE or omits the match argument, which selects approximate matching. For identifiers and other exact lookups, use FALSE and verify the selected range and return-column number.

Can VLOOKUP search for a value in a column to the right and return a value on the left?
No. VLOOKUP searches the first column in its range and returns values from columns to its right. Use XLOOKUP in Microsoft 365 or Excel 2021 and later, or rearrange the source data if you need a leftward result.

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.