Excel VLOOKUP Using Two Sheets: Steps and Examples

Formulas & functions · Updated November 19, 2026

To use VLOOKUP across two sheets in the same Excel workbook, enter a formula that names the source sheet in its table range, such as =VLOOKUP(A2,Products!$A$2:$C$100,3,FALSE). This looks for the value in A2 in the first column of the Products sheet and returns the matching value from that range’s third column.

How VLOOKUP works across sheets

VLOOKUP does not need a special cross-sheet mode. Its lookup value can be on one worksheet while its table array—the range Excel searches—is on another. The formula is entered on the sheet where you want the answer to appear, and the sheet name is included in the table-array reference.

For example, suppose an Orders sheet contains product codes in column A, and a Products sheet stores product codes, names, and prices in columns A, B, and C. You can retrieve each product’s price beside its code. With product codes in Orders!A2:A50 and the product table in Products!A2:C100, enter this in Orders!B2:

With product codes in Orders!A2:A50 and the product table in Products!A2:C100, enter this formula in Orders!B2 to return the price:

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

If A2 contains a code found in Products column A, Excel returns the value from column C on that matched row. The dollar signs keep the table range fixed when you copy the formula down. The final argument, FALSE, requests an exact match, which is usually the right choice for IDs, names, and product codes.

Set up the formula step by step

First check that the source table has the lookup value in its leftmost column. VLOOKUP searches only that first column, then returns a result from a column to its right. In this example, the code must be in Products column A if the formula’s table range begins at A.

  1. Open the sheet where the result should appear, such as Orders, and select the first result cell, such as B2.
  2. Type =VLOOKUP(, then select the cell containing the value to find. Here that is A2.
  3. Type a comma and enter the source-sheet range. You can type Products!$A$2:$C$100 or select the range on the Products sheet while building the formula.
  4. Type a comma and enter the number of the return column counted from the left edge of the selected range. The price is in column 3 of A:C, so use 3.
  5. Type a comma and FALSE for an exact match, close the parenthesis, and press Enter.
  6. Check that the displayed result matches the source record, then copy the formula down for the remaining orders.

For the example with order codes in Orders!A2:A50 and product data in Products!A2:C100, this is the complete formula in Orders!B2:

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

In Excel for Microsoft 365 on Windows, you can also build the range by clicking its sheet tab and dragging across the cells while the formula is being edited. Excel inserts the sheet reference. The same basic formula syntax works in Excel for Mac and Excel for the web; the interface for selecting ranges may differ slightly. Some regional settings use semicolons rather than commas between arguments, depending on the computer’s list-separator settings.

Refer to sheets with spaces in their names

A sheet name with spaces or certain punctuation needs single quotation marks around it in the reference. For example, if the source sheet is named Product List, use 'Product List'!$A$2:$C$100. The exclamation mark separates the sheet name from the cell range.

With order codes in Orders!A2:A50 and the product table in Product List!A2:C100, this formula in Orders!B2 returns the price from the third column of that table:

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

Excel generally adds the quotation marks automatically when you select a range on a sheet whose name requires them. Sheet names are not case-sensitive in ordinary references. If you rename the sheet using the tab’s Rename command, Excel typically updates formulas that refer to it. A deleted or misspelled sheet reference, however, can cause a reference error.

Understand the range and column number

The table-array argument defines both where VLOOKUP searches and which columns it can return. The lookup column must be the first column in that range. The col_index_num argument counts columns from the range’s left edge, not from the worksheet’s A column.

For instance, if the selected range is Products!B2:D100, then B is column 1, C is column 2, and D is column 3 within that range. A formula using 3 returns from worksheet column D. If you change the table range to A2:D100, the same 3 returns from worksheet column C instead. This is a common reason a formula returns a plausible but incorrect field.

Choose a range wide enough to include the return column but no wider than necessary. Using full-column references can be convenient, such as A:C, but bounded ranges make it easier to see the intended table and can avoid including unrelated data. When rows may be added, an Excel Table is another useful option: structured references can expand as table rows are added, though the VLOOKUP formula must still have the lookup field at the left of the chosen columns.

Exact matches, duplicates, and data types

Use FALSE as the final argument for most ordinary lookup tasks. It tells Excel to find an exact match rather than an approximate one. If you omit the last argument, VLOOKUP uses approximate-match behavior, which can return an unexpected result when the first column is not sorted as required. For a product code or employee ID, an approximate result is generally not appropriate.

If the lookup column contains duplicate values, VLOOKUP returns the first matching row it encounters, moving from top to bottom. It does not report all matches. If repeated codes are possible, decide which record should be returned or use a method designed to return multiple results.

Values that look alike may not match if one is stored as text and the other as a number. For example, a text code such as “0042” is not necessarily the same value as the number 42; converting the number to text also does not automatically restore leading zeros. Check the source and lookup columns for consistent data types and formatting. Leading or trailing spaces can also prevent text values from matching. Clean or standardize the source data when possible rather than changing values blindly.

Copy formulas and handle missing results

When you copy a formula down, the lookup cell reference should usually change from A2 to A3, while the source table range should stay fixed. That is why the example uses dollar signs in $A$2:$C$100. Without them, a relative range may shift as the formula is copied and eventually exclude the first rows of the source table.

With each order’s product code in column A and the fixed product list in Products!A2:C100, enter this in Orders!B2 to show a friendly message when no exact match exists:

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

IFERROR replaces any error from the enclosed formula with the text Not found. It can make a report easier to read, but it can also conceal a different issue, such as an incorrect range or column number. During setup, consider checking the raw result first so you can distinguish a genuinely missing code from a formula mistake. Once the formula is verified, a fallback message can be useful in a report.

Common VLOOKUP problems and fixes

  • #N/A: No exact match was found. Check spelling, spaces, leading zeros, number-versus-text storage, and whether the lookup value actually exists in the source’s first column.
  • #REF!: The column index is larger than the number of columns in the table range, or a referenced sheet or cell range has been deleted. Confirm the range width and the sheet name.
  • Wrong field returned: Recount the column index from the left side of the selected range. The index is relative to the range, not the worksheet.
  • Formula changes incorrectly when copied: Add dollar signs to the source range, for example $A$2:$C$100, while leaving the row-specific lookup cell relative.
  • Formula is displayed as text: Check whether the cell is formatted as Text or whether an apostrophe appears before the equals sign. Change the cell to General and re-enter the formula.

Also confirm that calculation is working and that the formula begins with an equals sign. If you are looking up a value that appears to be present but gets #N/A, inspect the actual cell contents; formatting can make a number and text string look identical on screen.

VLOOKUP versus XLOOKUP

VLOOKUP remains useful when a workbook already uses it or when compatibility with older Excel versions matters. Its main constraints are that the lookup field must be the first column in the selected range and the return column is chosen by a numeric position. Inserting or rearranging columns can make that index less intuitive.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It lets you specify the lookup column and return column separately, and it can search in either direction. With product codes in Products!A2:A100 and prices in Products!C2:C100, enter this in Orders!B2:

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

This returns the corresponding price or Not found when there is no match. For a workbook that must open in older Excel releases, verify that users’ versions support the functions you use; otherwise, VLOOKUP with FALSE is a broadly familiar option. Both approaches rely on the source data being structured consistently.

Frequently Asked Questions

Can VLOOKUP search a different sheet in the same workbook?
Yes. Include the source sheet name and an exclamation mark before the range, as in Products!$A$2:$C$100. If the sheet name contains spaces, put it in single quotation marks.

Does the lookup column have to be the first column?
Yes. VLOOKUP searches the leftmost column of its table array. To return a value, the return column must be within the selected range and to the right of that first column. Reorder the source range or use XLOOKUP if the lookup field is not on the left.

Why does VLOOKUP return #N/A when the value is there?
The visible values may differ in their underlying contents. Check for extra spaces, text-versus-number differences, leading zeros, and small spelling differences. Make sure the formula searches the correct first column and uses FALSE for an exact match.

Can VLOOKUP search a sheet in another workbook?
Yes. You can select a range in another open workbook while building the formula, and Excel creates an external reference. The reference may include the workbook name and file path. If the source workbook is moved or renamed, the link may need updating, so keep linked files accessible and verify the formula after moving them.

Can VLOOKUP return more than one matching row?
No. Standard VLOOKUP returns one result: the first matching row. If you need every match, use a suitable dynamic-array approach such as FILTER in Microsoft 365 or Excel 2021 and later, or filter the source data directly.

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.