VLOOKUP Excel Using Two Spreadsheets: Steps and Tips

Yes. VLOOKUP can find a value in one Excel workbook and return related information from another; the workbooks do not have to be combined. With the lookup ID in A2 of your current workbook and IDs, product names, and prices in columns A:C of the Products sheet in PriceList.xlsx, use =VLOOKUP(A2,'[PriceList.xlsx]Products'!$A$2:$C$500,3,FALSE) to return the matching price. The source workbook can be open or closed, though Excel may show a full file path in the reference when it is closed.

What “two spreadsheets” means in Excel

People often use “spreadsheet” to mean either a worksheet (a tab inside a workbook) or a separate workbook file. VLOOKUP can work across either arrangement. Two tabs in one workbook are the simpler case; two separate files require an external workbook reference, which points to the file, its sheet, and the range to search.

In the example above, the workbook containing the formula is the destination workbook. It has the value to look up in A2. PriceList.xlsx is the source workbook, and its Products worksheet contains the search table. VLOOKUP searches the first column of that table—in this case, column A—and returns a value from the requested column in the same matching row.

VLOOKUP does not merge the two files or copy the source data into the destination. It calculates a result based on the referenced range. That distinction matters if you later move, rename, or delete the source file: the formula may need its link updated, even though its visible result was previously correct.

How the cross-workbook formula works

The formula has four arguments: the value to find, the table range, the number of the result column within that range, and whether to require an exact match. In the example, A2 is the lookup value. The external reference identifies the table in another workbook. The number 3 means “return the third column of the selected range,” and FALSE requests an exact match.

The column number is relative to the selected range, not necessarily the worksheet’s lettered columns. If the range begins in column A, its third column is C. If the range begins in column D, its third column is F. VLOOKUP can return a result only from a column to the right of the first column in its table range; it cannot look left.

The dollar signs in the range make its row and column references absolute. This is useful when filling the formula down: A2 changes to A3, A4, and so on, while the source range remains fixed. FALSE is important for identifiers, names, and most ordinary lookups. With TRUE or an omitted final argument, VLOOKUP can use approximate matching, which requires the first column to be sorted and can return an unexpected result if it is not.

Prepare the workbooks before writing the formula

Before creating a link, confirm that both workbooks contain the intended data and that the source table has one record per lookup key. The first column of the selected source range must hold the values being searched. Check that IDs are consistently stored as numbers or consistently stored as text: a numeric 1042 and a text value containing 1042 may not match as expected.

Give the source worksheet a clear, stable name, such as Products, and save the source workbook in a location that will remain available.
Place the lookup key in the first column of the source range and the value you want returned in a column to its right.
Remove leading or trailing spaces from keys where appropriate, and check for duplicate keys. VLOOKUP returns the first match it encounters.
Decide which source column to return, then count its position from the left edge of the selected range.

For example, if column A contains product codes, B contains descriptions, and C contains prices, a range beginning at A can return a description with column index 2 or a price with index 3. If you select B:C instead, VLOOKUP will search descriptions in B; it cannot search product codes in A while using a range that starts at B.

Enter the formula using Excel’s interface

Save both workbooks first. Open the destination workbook and, if convenient, open the source workbook too. Opening both makes it easier to select the source range directly and reduces the chance of mistyping a workbook or worksheet name.

In the destination workbook, select the cell where the result should appear.
Start a VLOOKUP formula and select the cell containing the value to find, such as A2.
Enter the argument separator, then switch to the source workbook and select the table range, including its first lookup column and the return column.
Enter the return-column number counted from the left edge of that range, followed by FALSE for an exact match, and close the formula.
Press Enter and check the returned value against the source row. If it is correct, fill the formula down for the remaining records.

In Microsoft 365 for Windows, pressing Enter confirms a formula. To fill down, select the formula cell and drag its fill handle, or copy it and paste it into the cells below. The Mac versions offer the same basic formula and fill-handle workflow; menu locations can vary slightly by release. Excel for the web can calculate external workbook links in supported circumstances, but link creation and editing may be more limited than in the desktop app. If the source reference is not available or Excel cannot update it in the browser, use desktop Excel to establish or repair the link.

Using the formula when the source workbook is closed

Once the source workbook is saved, Excel can generally retain an external reference and calculate from it while the source file is closed. The displayed reference may include the drive and folder path as well as the workbook name and worksheet. With lookup IDs in A2 and source data in Products.xlsx, cells A2:C500, a closed-file reference can look like =VLOOKUP(A2,'C:\Reports\[PriceList.xlsx]Products'!$A$2:$C$500,3,FALSE). For that layout, if A2 contains a code found on a source row whose third-column value is 19.95, the result is 19.95.

The path in that example is illustrative; replace it with the actual location of your file. When you select cells from an open source workbook, Excel normally constructs the reference for you. If the file is moved, renamed, or stored in a different folder, Excel may ask to update links or may report that it cannot find the source. Keep linked workbooks together in a stable folder, and use Excel’s prompts or link-management options to point to the correct file rather than guessing at a path.

A formula that returns an old value does not necessarily prove the source link is current. External-link update prompts and calculation settings can affect when results refresh. If the source data changes, open the workbooks and confirm that the destination result updates. For regularly shared workbooks, tell recipients where the linked source file is stored and whether they need access to it.

Common errors and practical fixes

A #N/A result usually means Excel did not find an exact match, but it can also reflect inconsistent data. Check that the lookup value actually exists, that text and numbers have compatible types, and that there are no extra spaces or invisible characters. Also confirm that the source range begins with the lookup column and includes the expected rows.

A #REF! error often indicates that a referenced range, sheet, or workbook link is no longer valid. Check whether the source file or worksheet was renamed, moved, or deleted. A wrong but plausible result is often caused by an incorrect column index, duplicate lookup keys, or using approximate matching instead of FALSE. Confirm the selected range and inspect the matching source row rather than assuming the formula’s first result is the intended one.

After you verify the underlying issue, you can choose to display a friendlier message for missing matches. With lookup keys in A2 and source IDs and prices in Products.xlsx, range A2:C500, this example returns Not found when VLOOKUP produces an error and otherwise returns the price: =IFERROR(VLOOKUP(A2,'[PriceList.xlsx]Products'!$A$2:$C$500,3,FALSE),"Not found"). IFERROR also catches errors other than a missing match, so it can hide a broken reference or another formula problem. During setup, it may be better to inspect the original error; add error handling only when that behavior is appropriate for the finished sheet.

When VLOOKUP is not the best choice

VLOOKUP is useful when the lookup key is in the leftmost column of a simple table and the return value is to its right. It is familiar, widely supported, and adequate for many fixed lists. Its limitations become more noticeable when columns are rearranged or the value to return is to the left of the key: inserting a column can make a hard-coded column number point to a different field, and VLOOKUP cannot return a value from a column to the left.

In Microsoft 365 and Excel 2021 or later, XLOOKUP is a more flexible alternative for many tasks. It lets you specify the lookup range and return range separately, so the return range can be on either side of the lookup range. It also has an optional not-found result. XLOOKUP can use references to another workbook, but the source workbook must still be reachable for reliable link updates. Older Excel versions may not recognize XLOOKUP, so VLOOKUP can be a safer choice when the file will be shared with users of older releases.

Another option is to copy or import the source data into the destination workbook, especially when users need a self-contained file or the external source is unstable. That creates a snapshot rather than a live connection unless you set up a refreshable import process. Choose based on whether the result should update from the source, whether recipients can access it, and how often the source structure changes.

Frequently Asked Questions

Can VLOOKUP search a different Excel file?
Yes. Reference the source workbook, worksheet, and table range in the table-array argument. The lookup value can remain in the destination workbook, and the source workbook can often be closed after the link has been created and saved.

Do both workbooks have to be open?
No. Excel can usually calculate a saved external reference with the source workbook closed. Opening both is helpful when creating the formula or repairing a link, and access to the source file is still needed to refresh data from it.

Can VLOOKUP return a value from a column to the left?
No. The lookup column must be the first column in the selected table range, and VLOOKUP returns from a column to its right. Reorder the source table or use XLOOKUP in Microsoft 365 or Excel 2021 and later if compatibility permits.

Why does VLOOKUP return #N/A even though the value appears in both files?
The values may not be identical to Excel. One may be text and the other a number, or a cell may contain extra spaces or hidden characters. Check the source range’s first column and compare the actual stored values, not only how they look on screen.

Will the formula still work if I email only the destination workbook?
It may show a stored result, but it cannot reliably refresh from a source file the recipient cannot access. Send the source workbook too, provide access to its shared location, or make a deliberate copy of the needed data into the destination workbook.

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.