AI Excel Generator
← ALL GUIDES

How to Use VLOOKUP Between Two Sheets in Excel

Updated November 4, 2026 · AI Excel Generator

VLOOKUP between two sheets lets you find a value in one worksheet and return related information from another.

A few details about sheet names, lookup columns, and match type determine whether the formula returns the result you expect.

VLOOKUP syntax for another sheet

The VLOOKUP function searches for a value in the first column of a range and returns a value from another column in the same range.

Its syntax is:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

  • lookup_value is the value to find, such as a product ID in cell A2.
  • table_array is the range to search.

    It can be on a different worksheet.
  • col_index_num is the position of the return column within the selected range, counting the first column as 1.
  • range_lookup controls matching.

    Use FALSE for an exact match, which is typical for IDs and names.

When the source data is on another sheet in the same workbook, write its worksheet name followed by an exclamation mark before the range, as in Products!$A$2:$C$100.

Example: Look up a product price on another sheet

Suppose the current sheet, Orders, has product IDs in column A.

The Products sheet has IDs in column A, product names in column B, and prices in column C.

To return the matching price in Orders!B2, use:

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

If Orders!A2 contains P-104 and the matching row on Products has 24.99 in its third column, the expected result is 24.99.

Copy the formula down to look up the other order IDs.

The dollar signs keep the source range fixed as the formula is copied, while the relative reference A2 adjusts to A3, A4, and so on.

How to reference a sheet name with spaces

Worksheet names that contain spaces or certain punctuation need single quotation marks around the name.

For example, if the source worksheet is called Product List, use:

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

Excel may add these quotation marks automatically when you select a range on another sheet while building the formula.

The exclamation mark still separates the sheet name from the cell range.

Choose the correct lookup range and column number

VLOOKUP searches only the leftmost column of its table_array.

In the product example, the ID must be in column A of the selected range, and the price must be to its right.

The return column number is counted from the start of that range, not from the worksheet itself.

For example, in $A$2:$C$100, column A is 1, B is 2, and C is 3.

If you instead select $B$2:$C$100, column C is number 2.

A column index larger than the number of columns in the range returns #REF!.

Keep the lookup column at the left edge of the range.

If the value you want to find is in column C and the result is in column A, VLOOKUP cannot return a value to the left.

Reorder or copy the data into a suitable range, or use a function such as XLOOKUP where available.

Use exact matching for most cross-sheet lookups

Set the fourth argument to FALSE to find an exact match.

This avoids approximate results when the lookup column is unsorted or contains identifiers, names, or other distinct values:

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

If you omit the fourth argument, VLOOKUP uses approximate matching.

Approximate mode expects the first column to be sorted in ascending order and may return the nearest lower match instead of an exact one.

It is intended for tasks such as finding a tax rate from sorted thresholds, not usually for matching product IDs.

Common errors and how to troubleshoot them

  • #N/A: The lookup value may not exist in the first column of the selected range.

    Check for extra spaces, different punctuation, or mismatched data types, such as a number stored as text on one sheet and a numeric value on the other.
  • #REF!: The column index is greater than the number of columns in the range, or the referenced cells were deleted.

    Recount the columns from the left edge of the selected range.
  • Wrong result: Confirm that the fourth argument is FALSE when you need an exact match.

    Also verify that the lookup value appears in the first column of the range and that the column index points to the intended result.
  • Formula parse or name error: Check the worksheet spelling and quotation marks.

    Depending on regional settings, your spreadsheet may require semicolons instead of commas between function arguments.

To diagnose a failed match, compare the source and lookup cells directly.

For text values, invisible leading or trailing spaces can prevent a match; cleaning imported data with functions such as TRIM may help.

Avoid changing number formatting alone as a fix: formatting changes how a value looks, but does not necessarily change whether it is stored as text or as a number.

VLOOKUP across separate workbooks

You can also reference a range in another workbook.

In Excel, an external reference typically includes the workbook name, worksheet, and range.

For example:

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

The source workbook may need to be accessible for Excel to update the result, and the exact reference can depend on where the workbook is saved.

If you rename or move the source file, check the link.

Google Sheets uses a different approach for data in another spreadsheet: IMPORTRANGE can bring the source range into the current file, after which you can look up values in the imported data.

Compatibility and alternatives

VLOOKUP is supported in widely used versions of Microsoft Excel and in Google Sheets, making it a practical choice for shared spreadsheets.

In newer Excel versions, XLOOKUP can search in either direction and lets you specify an exact match without a numeric column index.

For example, to find an ID in Products!A2:A100 and return a price from Products!C2:C100, use:

=XLOOKUP(A2,Products!$A$2:$A$100,Products!$C$2:$C$100,"Not found")

XLOOKUP is not available in some older Excel releases.

If a workbook must work in older versions, VLOOKUP with an exact-match argument remains a broadly compatible option.

In Google Sheets, both VLOOKUP and XLOOKUP are available, though features can vary across spreadsheet products and editions.

Handle missing matches with IFNA

If a missing ID is expected, wrap the lookup in IFNA to show a clearer message instead of #N/A:

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

This displays Product not found when VLOOKUP cannot find an exact match.

It does not hide other formula problems, such as an invalid column index returning #REF!, so correct the underlying range and index errors separately.