VLOOKUP Between Two Excel Workbooks: A Simple Guide

Formulas & functions · Updated November 18, 2026

Yes. In Excel for Microsoft 365 on Windows, VLOOKUP can search a table in another workbook by using an external workbook reference; if the value to find is in A2 and the other workbook has lookup data in columns A:C on its Inventory sheet, enter =VLOOKUP(A2,'[ProductCatalog.xlsx]Inventory'!$A:$C,3,FALSE) to return the matching value from column C.

What VLOOKUP does across workbooks

VLOOKUP searches down the first column of a table for a value, then returns a value from another column on the same row. The lookup table can be in the workbook where you are writing the formula or in a different Excel workbook. When it is in a different file, the formula includes a link to that file and the worksheet containing the table.

For example, imagine an Orders workbook with product codes in column A. A separate ProductCatalog.xlsx workbook has product codes in column A, descriptions in column B, and prices in column C on a worksheet named Inventory. VLOOKUP can match an order’s product code to the catalog and return the price, without copying the catalog into the Orders workbook first.

The formula does not search every column or identify a match based on a description elsewhere in the row. Its basic rule is important: the value you are looking up must be in the leftmost column of the table range, and the return column must be to its right.

Understand the external-reference formula

In the example formula, A2 is the value being searched for. The external reference identifies ProductCatalog.xlsx, its Inventory worksheet, and columns A through C as the lookup table. The number 3 tells VLOOKUP to return a value from the third column of that selected range, while FALSE requests an exact match.

If A2 contains P-104 and the catalog has P-104 in Inventory!A17 with a price of 18.50 in Inventory!C17, the example returns 18.50. The row number does not need to match between the two workbooks; VLOOKUP finds the matching code in the catalog and returns the value from that catalog row.

The column number is counted from the left edge of the selected table, not from the worksheet’s overall column letters. With a range beginning in column A, column C is the third column. If you select columns B:D instead, column D is also the third column of that range.

Dollar signs in the range make it absolute. If you copy the formula down, the lookup value changes from A2 to A3, while the catalog range remains fixed. Full-column references are convenient for a growing list, though a precisely bounded range can be more efficient in very large workbooks.

Build the link in Excel for Windows

  1. Open the workbook where you want the results and, if possible, open the workbook containing the lookup table. Confirm the lookup values are in the first column of the table and the return values are in a column to the right.
  2. Select the cell where the result should appear. Type the beginning of the function and identify the cell containing the value to find, followed by a comma.
  3. Switch to the catalog workbook and select the lookup range, starting with its key column and extending through the column to return. Excel can insert the external reference for the selected range.
  4. After the range, enter a comma, the return-column number, another comma, and FALSE for an exact match. Close the parenthesis and press Enter.
  5. Check the result against a known record, then fill the formula down for the remaining rows. Keep the source workbook and its worksheet name unchanged until you know the link is working as expected.

It is also fine to type the reference directly if you know the workbook and sheet names. When a sheet name or file name includes spaces, Excel uses apostrophes around the external reference, as in the example. Selecting the range with the mouse is often the easiest way to avoid typing the name incorrectly. Excel may add a full folder path to the reference when the source workbook is closed; that is normal.

Excel for Mac supports VLOOKUP and external workbook references too, although ribbon placement and file selection details can differ. Excel for the web can calculate many workbook formulas, but access to linked files depends on permissions and where the files are stored. If a link behaves differently in a browser, open the files in the desktop app to inspect or repair it.

Why exact match is usually the right choice

For IDs, product codes, names, and other values that must match precisely, use FALSE as the final argument. If you omit the final argument, VLOOKUP uses approximate matching by default. Approximate matching is intended for ordered data and can return an unexpected result when the first column is not sorted correctly. That default is a common source of quietly incorrect lookups.

An exact match still requires the key values to be genuinely equivalent. A number stored as text may not match the same digits stored as a number, and a code with a leading or trailing space can fail to match the clean version. Also, if the lookup column contains duplicate keys, VLOOKUP returns the first matching row it encounters. It does not warn you that another matching record exists later in the table.

Before relying on the results, check whether keys should be unique, whether blanks are meaningful, and whether the source data uses consistent types and formatting. For important reports, compare several returned values to the source workbook rather than assuming that a formula filled down is automatically correct.

Prepare the lookup data

A little preparation makes a cross-workbook lookup much easier to maintain. The key should identify the record consistently in both files. If one workbook uses a product code and the other uses a product name, VLOOKUP cannot infer that they refer to the same item; you need a shared identifier or a deliberate mapping table.

  • Put the lookup key in the first column of the selected range, with the return field in a column to its right.
  • Remove accidental leading and trailing spaces, and use a consistent type for keys such as numeric IDs or text codes.
  • Check for duplicate keys if each key is supposed to identify only one record.
  • Use clear worksheet and workbook names, and avoid renaming or moving linked files while a report is in use.
  • Decide whether the lookup should return a blank, a message, or an error when a key is not found, and handle that outcome consistently in the report.

Do not sort the table just to make an exact-match lookup work; FALSE does not require sorted data. Sorting can still help people review the source, but it is not a substitute for a clean key column. If the source contains a formatted Excel table, a regular cell-range reference is often the simplest external VLOOKUP setup.

Common errors and how to fix them

#N/A: Excel did not find an exact match. Check for spelling differences, spaces, mismatched text and number types, or a key missing from the source. Confirm that the searched value is actually in the first column of the range. A blank lookup cell can also produce a result that is confusing if the source contains blank keys.

#REF!: The reference may point to a workbook, worksheet, or range that has been deleted or changed. Check whether the file was renamed or moved, and use Excel’s link controls to locate the source. If the workbook is on a shared drive or cloud service, verify that you have access to the file and that it has not been relocated.

Wrong value: Confirm the return-column number. It counts columns inside the selected range, not worksheet letters. Also check whether the first matching key is a duplicate and whether the formula uses FALSE. An omitted match argument can lead to approximate results that look plausible but are not the intended record.

Formula appears as text: Check whether the destination cell is formatted as Text. Change it to General, then re-enter the formula. Also make sure the entry begins with an equals sign and that Show Formulas has not been turned on for the worksheet.

Formula changes when copied: If the lookup range shifts as you fill down, make it absolute with dollar signs, as in the example’s $A:$C. The lookup cell reference should usually remain relative so each row searches for its own key.

Keeping workbook links reliable

An external-reference formula depends on the source file remaining available. If you rename or move the catalog, Excel may ask to update the link or may show an error until it can find the new location. A link can also behave differently for another person if the formula points to a folder on your computer that they cannot access.

For shared work, store linked workbooks in a location the intended users can reach, such as an approved shared folder or a cloud location with appropriate permissions. Test the workbook from the same kind of access the recipients will use. Keep a stable source-file name and folder where practical, and document which file is authoritative if several copies exist.

External links can also raise a trust question: a workbook may prompt you to update links when opened. Only update links from a source you recognize and are authorized to use. If you do not need live updates, you can copy results and use Paste Special > Values to keep fixed results, but those values will no longer refresh when the catalog changes. Keep a copy of the formula-based workbook before replacing formulas with values.

When another approach is a better fit

VLOOKUP is a useful choice when the key is in the first table column, the return field is to its right, and the formula needs to be easy for other spreadsheet users to understand. It is not ideal when the table layout changes frequently: inserting or deleting columns within the lookup range can make a hard-coded return-column number point to a different field.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It allows the lookup and return ranges to be specified separately, can return values from either side of the lookup column, and uses exact matching by default. Check compatibility before using it in a workbook that must be opened in older Excel versions.

Power Query is often preferable when you need to combine two larger tables, repeat the merge on updated files, or clean data as part of the process. In Excel for Microsoft 365 on Windows, start with Data > Get Data > From File > From Excel Workbook to bring in the source, then use the query merge tools to join tables on matching columns. Power Query creates a repeatable import-and-transform process rather than a separate formula in every result row; refresh it when the source data changes. Menu availability and some capabilities can vary by Excel platform.

For a small lookup that needs to update immediately as values change, an external VLOOKUP is usually more direct than importing data. For a recurring, multi-step data preparation task, a query may be easier to refresh and audit. The best method depends on workbook size, compatibility requirements, how often the source changes, and whether users need live formula results or a refreshed combined table.

Frequently Asked Questions

Can VLOOKUP work if the other workbook is closed?
Yes, VLOOKUP can use an external reference to a closed workbook. Excel may store the source file’s full path in the link. The source must remain accessible at that location, and moving or renaming it can require the link to be updated.

Do both workbooks need to be open?
No. Opening both files can make it easier to select the lookup range while building the formula, but the source workbook does not have to stay open for a standard VLOOKUP external reference to calculate.

Why does VLOOKUP return #N/A when the value looks identical?
Look for hidden spaces, different data types, or a lookup value that is not in the first column of the selected range. Check the source cell’s actual contents as well as how it is displayed; formatting can make different underlying values look alike.

Can VLOOKUP return a value from a column to the left?
No. The return column must be to the right of the lookup column in the selected range. XLOOKUP can return from either direction, or you can rearrange the source data or choose another lookup method.

What happens if the source file is moved?
The link may stop resolving until Excel can locate the workbook again. Restore the file to its original location or update the workbook link to the new location, then verify that the returned values are correct.

Related

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

More in Formulas & functions

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

Spotted an error in this guide? Let us know.