VLOOKUP Between Two Sheets: Examples and Common Fixes

To use VLOOKUP across two Excel sheets, put the lookup value on one sheet and the data table on the other, then reference that sheet in the table-range argument. For example, with a product code in Lookup!A2 and codes in Products!A2:A100, use =VLOOKUP(A2,Products!$A$2:$C$100,3,FALSE) to return the matching value from the third column of the Products table.

A basic VLOOKUP example across two sheets

Imagine a workbook with a sheet named Products and a sheet named Lookup. On Products, column A contains product codes, column B contains product names, and column C contains unit prices. On Lookup, column A contains the code you want to find. If Lookup!A2 contains P-104 and the matching row on Products has a price of 18.5 in column C, the formula returns 18.5; format the result cell as Currency to display it as $18.50.

Assuming the product codes are in Products!A2:A100, product names are in column B, prices are in column C, and the code to find is in Lookup!A2, enter this formula in Lookup!B2:

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

The formula can refer to A2 without naming Lookup because it is entered on that sheet. The table reference does name Products, so Excel searches there. The dollar signs keep the table range fixed when you copy the formula down. The final FALSE tells Excel to find an exact match, which is usually the right choice for product codes, employee IDs, invoice numbers, and similar identifiers.

How the formula works

VLOOKUP has four arguments: the value to find, the table range, the number of the column to return, and whether to use an exact or approximate match. Its name describes its limitation: it searches down the first column of a range, then returns a value from a column to the right on the same row. In the example, the search starts in column A of Products, and the return value comes from the third column of the selected range, column C.

Lookup value: A2, the product code on the current sheet.
Table range: Products!$A$2:$C$100, the other sheet’s data area.
Column index: 3, meaning the third column within that selected range, not worksheet column C in every possible setup.
Match setting: FALSE, requiring an exact match rather than an approximate one.

The range must begin with the column containing the values to search. If the product codes were in column B and prices in column D, the range could start at B and the return index would be 3, because D is the third column in B:D. The index is relative to the selected range, a detail that commonly causes incorrect results.

Set up and copy the formula

For a reliable lookup, first make sure the lookup column and the source table are arranged consistently. The lookup key must appear in the first column of the chosen table range, and the value you want returned must be somewhere to its right.

Put a clear, unique identifier in the source table, such as a product code, rather than relying on a name that may be repeated.
On the lookup sheet, place the code to search in a cell such as A2.
Select the result cell, such as B2, and enter the VLOOKUP formula with the source sheet name and range.
Use FALSE for an exact match unless you specifically need approximate matching on sorted numeric data.
Copy the formula down for additional lookup values. Keep the table range absolute with dollar signs so it does not shift as the formula is filled.

In Microsoft 365 for Windows, you can drag the fill handle at the lower-right corner of the selected formula cell, or copy and paste the cell. The Windows keyboard shortcut for copy is Ctrl+C and paste is Ctrl+V. On Mac, use Command+C and Command+V. The formula and sheet-reference syntax work the same in Excel for Mac; localized settings may use different argument separators, so the examples here use commas as requested.

Referencing sheets with spaces or special characters

A simple sheet name such as Products can be followed by an exclamation mark with no quotation marks: Products!A2:C100. If a sheet name contains spaces or certain punctuation, enclose it in single quotation marks. For example, a sheet called Product List is referenced as ‘Product List’!A2:C100. Excel may add the single quotation marks automatically when you select the range on that sheet while building a formula.

Assuming Product List has codes in A2:A100, names in B, prices in C, and Lookup!A2 contains the code, this formula returns the price from the third range column:

=VLOOKUP(A2,'Product List'!$A$2:$C$100,3,FALSE)

Use the sheet tab’s actual name, including spaces. A reference such as ProductList!A2:C100 will not refer to Product List unless the tab is named exactly ProductList. If a worksheet is renamed, Excel generally updates formulas that refer to it within the workbook.

Common errors and how to fix them

When a two-sheet lookup fails, check the reference and the underlying values before rebuilding the formula. The most frequent problems are a range that starts in the wrong column, an incorrect return-column number, or lookup values that look alike but are stored differently.

#N/A: With FALSE, this usually means no exact match was found. Check spelling, leading or trailing spaces, and whether one value is stored as text while the other is numeric. A number 104 and text value 104 can appear identical but fail to match.
#REF!: The column index is larger than the number of columns in the selected range, or the formula refers to a deleted range. For A:C, the largest valid index is 3.
Wrong result: Confirm that the intended lookup field is the first column of the selected range and that the column index points to the return field. Also check for duplicate keys: VLOOKUP returns the first matching row it encounters.
Formula changes when copied: Lock the source range with dollar signs, as in $A$2:$C$100. Leave the lookup cell relative if it should change from A2 to A3, A4, and so on.
Unexpected approximate result: Include FALSE explicitly. If the fourth argument is omitted or set to TRUE, VLOOKUP can use approximate matching; for that behavior, the first column must be sorted in ascending order.

Spaces can be hard to spot. Compare the actual cell contents, or clean the source data before lookup. Excel’s TRIM function can remove ordinary leading and trailing spaces from text, but imported data may contain nonbreaking spaces that TRIM does not remove. Also note that VLOOKUP is not case-sensitive: uppercase and lowercase versions of a text key are treated as equal.

Returning other fields and handling duplicates

To return a different field from the same table, change the column index while keeping the lookup range positioned over the same columns. With product codes in A, names in B, and prices in C, index 2 returns the name and index 3 returns the price. If you insert or rearrange columns inside the table, a hard-coded index may no longer identify the field you intended, so verify formulas after changing the source layout.

VLOOKUP returns one result, not every row that matches. If two products share the same code, the first matching row in the selected range is returned. A lookup key should ideally be unique. If the task is to retrieve all matching rows, use a filtering approach instead of expecting VLOOKUP to return a list.

You can also make an error message friendlier by wrapping the lookup in IFNA. Assuming the same Products table and lookup code in A2, this formula shows Not found when no exact match exists; otherwise it returns the price:

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

IFNA handles the #N/A result specifically. It does not hide other errors such as #REF!, which can be useful because a broken range should not be mistaken for a missing product.

VLOOKUP compared with newer lookup options

VLOOKUP remains useful when the search field is the leftmost column of the table and the return field is to its right. It is widely understood and works in older Excel versions. Its main constraints are that it cannot return a value to the left of the lookup column and that its numeric column index can become fragile if the table’s structure changes.

In Microsoft 365, XLOOKUP can search one range and return from another, including a column to the left, and lets you provide a not-found result directly. It is often a more flexible choice for new workbooks, but VLOOKUP may be preferable when a workbook must also work in older Excel releases that do not support XLOOKUP. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, not every legacy version. For a simple two-sheet table whose key is already in the first column, VLOOKUP with FALSE is still a clear and effective solution.

Frequently Asked Questions

Can VLOOKUP look up a value on another sheet?
Yes. Put the source table range in the second argument and prefix it with the source sheet name, such as Products!$A$2:$C$100. Use single quotation marks around sheet names that contain spaces.

Do both sheets need to be in the same workbook?
No, VLOOKUP can refer to a range in another workbook, but external links depend on the source file’s location and availability. Excel may include the workbook name and path in the reference. For routine use, keeping the source and lookup sheets in one workbook makes links easier to manage.

Why does VLOOKUP return #N/A even though the value appears in both sheets?
The values may differ invisibly or by data type: for example, one may be text and the other a number, or one may contain extra spaces. Check the source cells directly, standardize the key column, and confirm that the formula uses FALSE for exact matching.

Can VLOOKUP search from right to left?
No. It searches the first column of its table range and returns a value from a column to the right. Rearrange the table, use a different lookup method such as XLOOKUP in a supported Excel version, or use an INDEX-and-MATCH construction if compatibility requirements call for it.

What does the number 3 mean in a VLOOKUP formula?
It means return the value from the third column of the table range, counting from the range’s left edge. It does not mean worksheet column C unless the selected range begins in column A.

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.