VLOOKUP Examples Using Two Spreadsheets

To look up a value in a separate Excel workbook, put the lookup key in your current sheet and use VLOOKUP with a reference to the other workbook. For example, with a product code in A2 and a source workbook named SalesData.xlsx whose Products sheet has codes in column A and prices in column C, use =VLOOKUP(A2,'[SalesData.xlsx]Products'!$A$2:$C$100,3,FALSE). If A2 contains SKU-208 and that row has 8.00 in column C, the formula returns 8.00. The same approach works across two sheets in one workbook, though the reference is shorter.

What “two spreadsheets” can mean

People use “two spreadsheets” to describe two different setups: separate worksheets inside one Excel workbook, or separate workbook files such as Orders.xlsx and Products.xlsx. VLOOKUP can search either setup. The important difference is how you write the lookup range: a sheet reference for another tab in the same file, and an external-workbook reference for a different file.

In either case, VLOOKUP searches the first column of the range you specify. It finds the lookup value in that column, then returns a value from another column in the same row. It does not match records by row position alone, and it cannot search a return column that is to the left of the lookup column.

Example: look up data on another worksheet

Suppose an Orders sheet has product codes in A2:A50. A Products sheet in the same workbook has product codes in column A, descriptions in column B, and prices in column C. To retrieve the price for the code in A2, use the following formula. It assumes the product table is in Products!A2:C100, with the code in the first column and price in the third.

=VLOOKUP(A2,Products!$A$2:$C$100,3,FALSE)

If A2 contains SKU-208 and the matching row on Products has 8.00 in its third column, the result is 8.00. If the sheet name contains a space, enclose it in single quotation marks, as in ‘Product List’!$A$2:$C$100. The dollar signs keep the lookup range fixed when you copy the formula down; A2 remains relative so it changes to A3, A4, and so on.

Example: look up data in another workbook

For separate files, keep the source table arranged with the lookup field on the left. Imagine Orders.xlsx has the code to search in A2, while SalesData.xlsx has a Products sheet with codes in A, descriptions in B, and prices in C. If the two workbooks are open, Excel can build the reference when you start the formula, switch to SalesData.xlsx, and select the source range.

Assuming that layout and a source range of A2:C100, the formula is:

=VLOOKUP(A2,'[SalesData.xlsx]Products'!$A$2:$C$100,3,FALSE)

For a matching SKU-208 row with 8.00 in the third column, the result is 8.00. The workbook name appears in square brackets and the worksheet name follows it. Excel may add a folder path to the reference when the source workbook is closed, for example ‘C:\Reports\[SalesData.xlsx]Products’!$A$2:$C$100. Use the path Excel creates for the actual file location; do not assume that a short reference will keep working if the file is moved.

How to build the formula in Excel

Open the workbook where you want the result and, if possible, the workbook containing the source data.
Confirm which column contains the lookup key in the source. The key must be the leftmost column of the range you will select.
Select the result cell and type =VLOOKUP(, then select the cell containing the key, such as A2.
Type a comma, select the complete source range beginning with its key column, and type a comma followed by the return-column number.
Type a comma and FALSE to request an exact match, close the parenthesis, and press Enter.
Check the returned value against the source row. If appropriate, fill the formula down for the other records.

For Windows, Ctrl+C and Ctrl+V copy and paste; on Mac, use Command+C and Command+V. The formula itself works in Excel for Microsoft 365 on Windows and Mac. Excel for the web also supports VLOOKUP, but selecting ranges across open browser workbooks may be less convenient; entering or editing an external reference is often easier in desktop Excel. Menu names and the formula syntax do not change just because the workbook is on a different worksheet.

Understand the arguments and exact matching

The function has four arguments: the value to find, the table range, the number of the return column counted from the left edge of that range, and the match mode. In the example, 3 means the third column of A:C, not column C in every possible range. If you instead select B:D, a 3 returns from D.

FALSE requests an exact match, which is usually appropriate for product codes, employee IDs, invoice numbers, and other identifiers. If you omit the last argument, Excel uses approximate-match behavior. Approximate matching is intended for ordered ranges such as tax bands or commission thresholds and requires the first column to be sorted in ascending order. On an unsorted list, it can return an unexpected result. For ordinary record lookups, explicitly include FALSE.

Copying the formula and keeping references stable

After confirming the first result, copy the formula down. The lookup cell should normally change row by row, while the source range should remain fixed. In the example, A2 is relative and $A$2:$C$100 is absolute. Without the dollar signs, the range could shift as the formula is filled, eventually excluding the record you need.

When formulas refer to a different workbook, keep the source file in a stable location and avoid renaming or moving it after building the formulas. Excel may update references when you move or rename files through Excel, but references can break if files are moved outside Excel or the expected folder is unavailable. If a workbook opens with a prompt to update links, verify the source path and data before accepting a refreshed result.

Common errors and practical fixes

#N/A: No exact match was found. Check for leading or trailing spaces, different codes, or numbers stored as text in one file and as numbers in the other. Formatting a cell to look like a number does not necessarily change its underlying value.
Wrong value: Confirm the final argument is FALSE and that the lookup column is the first column in the selected range. Also verify that the column index points to the intended return field.
#REF!: The return-column number may be larger than the number of columns in the selected range, or the referenced source sheet or workbook may have changed.
Formula shown as text: The cell may be formatted as Text, or the formula may have a leading apostrophe. Change the cell to General, then edit and re-enter the formula.
Unexpected duplicates: VLOOKUP returns the first matching row it encounters. If the source contains repeated keys, clean the data or decide which record should be authoritative.

If you prefer a message instead of #N/A, you can wrap the lookup in IFERROR. With the lookup value in A2 and the same external source range and price column, this formula displays Not found for any error:

=IFERROR(VLOOKUP(A2,'[SalesData.xlsx]Products'!$A$2:$C$100,3,FALSE),"Not found")

Use that approach carefully: IFERROR also hides errors other than a missing match. During setup, it can be useful to see the original error so you can correct a broken reference or invalid column index rather than masking it.

When VLOOKUP is not the best choice

VLOOKUP is useful when the key is in the leftmost column and the return field is to its right. If the return field is to the left, rearrange the source if practical, or use a lookup function that can search in either direction. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; it can specify separate lookup and return ranges and has an optional not-found result. For example, it avoids counting a return-column index when the code and price columns are selected separately.

Another option is INDEX with MATCH, which works in older Excel versions and can also return values from columns to the left. Choose based on the workbook’s version requirements and who needs to open it. A newer function may be clearer for current Excel users but may not calculate in an older version. If you use VLOOKUP, the key-first-column layout is the essential constraint to remember.

Frequently Asked Questions

Can VLOOKUP work between two different Excel files?
Yes. Include the source workbook and worksheet in the table-range reference. Excel can create the reference when both files are open, and it may include the full folder path when the source file is closed.

Do both workbooks have to be open?
No. Excel can use an external reference to a closed workbook, provided the source file is still at the referenced location. Opening both files can make it easier to build or inspect the reference.

Why does VLOOKUP return #N/A when the value is visibly there?
The displayed values may not be identical underneath. Check for extra spaces, different text-versus-number types, or a slightly different code. Also confirm that the lookup value is in the first column of the range.

Can VLOOKUP return a value from a column to the left?
No. VLOOKUP searches the first column of its range and returns from a column to its right. Use XLOOKUP in supported Excel versions or INDEX and MATCH when you need a leftward lookup.

What does the 3 mean in the example?
It means the third column within the selected table range. For A:C, the third column is C. If the selected range begins in B, the count starts at B instead.

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.