VLOOKUP Not Working Across Workbooks? Fix Common Errors

VLOOKUP can search a range in another workbook, even when that workbook is closed, as long as the external reference points to the right file, sheet, and range. If the lookup value is in A2 and the source workbook has product codes in column A and the values to return in column D, use an exact-match formula such as =VLOOKUP(A2,'[Inventory.xlsx]Products'!$A$2:$D$500,4,FALSE); the checks below help when it returns an error or the wrong result.

How VLOOKUP works across workbooks

VLOOKUP does not have a special cross-workbook mode. It uses the same four arguments whether the table is on another sheet in the current file or in a separate file: the value to find, the table range, the column number to return, and whether to use an exact or approximate match. The difference is that the table range includes an external workbook and worksheet reference.

In the example above, A2 is the lookup value in the workbook containing the formula. The source range begins in column A, so VLOOKUP searches that column for the value in A2. The 4 tells it to return the matching row’s value from the fourth column of the selected range, which is column D. FALSE requests an exact match. For example, if A2 contains P-104 and that code’s row has 18 in column D, the formula returns 18.

The order of the source columns matters: VLOOKUP searches only the leftmost column of its table range. If the code is in column C and the value to return is in column A, selecting A:C will not make VLOOKUP search column C and return column A. You must rearrange the source or use a different lookup method.

Check the formula and its range

A frequent cause of failure is a range or column number that does not match the source layout. Count the return column from the first column in the table range, not from column A of the worksheet. If the range begins at B, then B is column 1, C is column 2, and D is column 3. A column number of 4 in that range would ask for a fifth worksheet column, not column D.

For a dependable setup, check these items before changing the formula:

Confirm that the file name and worksheet name match the source workbook, including spaces and punctuation.
Make sure the lookup column is the first column in the selected range.
Count the return column from the left edge of the range and check that the range includes it.
Use FALSE for an exact match unless you deliberately need an approximate match and have prepared the data for it.
Use dollar signs in the source range if you will copy the formula down, so the range does not shift.

In Excel for Microsoft 365 on Windows, you can create the reference without typing its full path: begin entering the formula, switch to the source workbook, select the range, and complete the remaining arguments. Excel inserts the workbook and sheet reference. This is often safer than manually typing a path with spaces or a long folder name.

Understand the external workbook reference

An external reference commonly looks like '[Inventory.xlsx]Products'!$A$2:$D$500. The square brackets contain the workbook name, Products is the worksheet, the exclamation mark separates the sheet from its range, and the dollar signs make the range absolute. When a workbook or sheet name contains spaces, Excel surrounds the relevant reference with apostrophes. Let Excel create the reference if you are uncertain about the punctuation.

When the source workbook is open, Excel may show a shorter reference with just its file name. When it is closed, Excel generally needs the file’s location as well as its name, and may store a full path in the link. You do not have to force a particular path format by hand: selecting the source range while building the formula lets Excel write the reference it needs.

A closed source file can still supply values, but it must remain accessible at the location Excel expects. If someone renames the file, moves it to another folder, changes a network location, or removes access to a shared drive, the formula may stop resolving. Reopening the source file does not fix a wrong location by itself; the link must point to the file’s current location.

Troubleshoot #N/A and mismatched values

#N/A usually means that an exact match was not found. It does not always mean the record is absent: the displayed values may look the same while the underlying values differ. A code stored as text in one workbook and as a number in the other is a common example. Leading or trailing spaces, nonprinting characters, and inconsistent punctuation can also prevent a match.

Compare the source value and lookup value directly. Check whether both are stored as numbers or both as text, and inspect spaces at the beginning or end. Do not convert identifiers to numbers automatically if leading zeros are meaningful: a code such as 0042 is not necessarily interchangeable with 42. For recurring data imports, standardize the columns consistently before looking up values. If you use a cleanup formula, verify its output against the original records, especially where codes have leading zeros or significant spaces.

Also check whether the lookup value is actually present in the first column of the selected source range. A value can exist somewhere else in the source sheet and still produce #N/A if it is outside the selected range or appears only in a column to the right of the range’s first column.

If you want a friendly message instead of #N/A, wrap the lookup in IFNA. With A2 holding the product code and the external Products range A2:D500 holding the source data, this formula displays Not found only when the lookup has no match:

=IFNA(VLOOKUP(A2,'[Inventory.xlsx]Products'!$A$2:$D$500,4,FALSE),"Not found")

IFNA does not repair the underlying cause. Use it for presentation after confirming that a missing match is a possibility you want to handle; otherwise it can make an unexpected data problem less visible.

Read other common errors correctly

#REF! points to an invalid reference. The source worksheet, workbook range, or return-column position may have changed, or the external link may no longer resolve. Recheck the range and source location. If a column was deleted from the selected range, the formula’s numeric column index may now point beyond that range.

#VALUE! can appear when an argument is invalid, including when the column index is not a usable positive column number. Check the third argument and make sure the requested return column is within the table range. A misspelled function name or malformed formula can produce a different error, so read the exact message rather than treating every failure as a broken file link.

A more troubling result is a plausible but incorrect value. If the fourth argument is omitted, VLOOKUP uses approximate matching rather than exact matching. Approximate matches are intended for ordered ranges and can return an unexpected result when the data is unsorted or an exact code is required. For IDs, names, invoice numbers, and most ordinary record lookups, specify FALSE explicitly.

Keep the lookup range stable when copying

When copying a formula down a column, a relative range can move one row at a time. That can make the first formula work and later formulas return #N/A or a value from the wrong range. In the example, the dollar signs in $A$2:$D$500 lock the source range while the reference to A2 changes to A3, A4, and so on. In Excel for Microsoft 365 on Windows, F4 toggles reference styles while editing a cell reference; on a Mac, the equivalent can depend on the keyboard’s function-key setting, and Fn+F4 may be needed.

Check the first, middle, and last copied formulas if results vary unexpectedly. The lookup-value reference should advance by row, but the external source range should remain fixed. Also confirm that the source range is large enough to contain new records. A range ending at row 500 will not include a new entry in row 501 unless you extend it or choose a suitable larger range.

Repair a moved or renamed source file

If formulas worked before a file move, focus on the workbook link rather than rewriting every lookup. In Excel for Microsoft 365 on Windows, open the formula workbook and look for Data > Workbook Links. There you can inspect linked workbooks and, where available, change the source location or update the link. The exact options shown can depend on the workbook and how the link was created.

If Excel cannot find the file, confirm the source is available to your account and that you have permission to open it. For shared files, a synced cloud folder or network location may appear differently to another person or computer. Moving both workbooks into a new folder can also change how a link is resolved; check the link from the computer that needs to use the formula. On Mac or Excel for the web, the interface for managing workbook links can differ from Windows desktop Excel, so use the link controls available in that version rather than assuming the Windows ribbon path is identical.

Be cautious when Excel asks whether to update external links. Updating can bring in current values from the source, while not updating can leave previously stored values in place. If you do not recognize the linked file or trust its origin, verify it before enabling updates. External links are references to other files, not proof that the data is current or correct.

When another lookup method makes more sense

VLOOKUP remains useful when the lookup column is at the left of a simple table and the return column is to its right. If you need to return a value from a column to the left, or want a formula that is less dependent on a numeric column index, consider XLOOKUP in Microsoft 365 or Excel 2021 and later. XLOOKUP can search in one range and return from another, so inserting a column into a table is less likely to require renumbering the return column.

INDEX with MATCH is another option when you need more control over lookup and return ranges, including in older Excel versions. Changing methods will not fix a broken external path, inconsistent data types, or a missing record; those checks still apply. Choose the method that fits the workbook’s Excel versions and how the source data is arranged, particularly if other people need to open and maintain the file.

Frequently Asked Questions

Does the source workbook have to be open for VLOOKUP to work?

No. An external VLOOKUP can refer to a closed workbook if the link points to a file Excel can access. Opening the source can help you inspect or rebuild the reference, but it is not generally required just to calculate the lookup.

Why does my cross-workbook VLOOKUP return #N/A?

The exact lookup value may not be in the first column of the selected source range, or the two values may differ in type, spaces, or characters. Confirm the range and compare the underlying values, not just how they appear on screen.

Why did the formula stop working after I moved the workbook?

The formula may still point to the old file location. Restore access to that location or use Excel’s workbook-link controls to update the source path, then check that the source file and worksheet are available.

Can VLOOKUP return a value from a column to the left?

No. VLOOKUP searches the leftmost column of its table range and returns a value from a column to its right. Rearrange the source data or use XLOOKUP or INDEX with MATCH when the return column is on the left.

Should I use TRUE or FALSE for an exact lookup?

Use FALSE for an exact match, particularly for identifiers and names. TRUE requests approximate matching, which has different behavior and is appropriate only when the lookup data and intended result support that kind of match.

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.