VLOOKUP Between Two Sheets in Excel: How It Works

Formulas & functions · Updated November 21, 2026

To use VLOOKUP between two sheets in Excel, put the lookup value in one sheet and refer to the data range on the other sheet. For example, with an order code in A2 and a Catalog sheet containing codes in column A and prices in column C, use =VLOOKUP(A2,Catalog!$A$2:$C$100,3,FALSE). This searches for an exact match in Catalog column A and returns the corresponding value from column C.

What VLOOKUP between sheets means

VLOOKUP searches down the first column of a selected range for a value, then returns a value from another column in the same row. The lookup range can be on a different worksheet in the same workbook, so you can keep source data on one tab and display matching information on another. The function itself works the same way whether the range is on the current sheet or a separate sheet; the sheet reference identifies where the range is.

A typical setup might have an Orders sheet with order IDs and product codes, and a Catalog sheet with product codes, descriptions, and prices. VLOOKUP can find each code in the catalog and bring its description or price into the order list. It does not move or copy the source data: the formula displays a result based on the referenced range, and that result updates when relevant source data changes and Excel recalculates.

How the formula works

VLOOKUP has four arguments: the value to find, the lookup range, the number of the return column within that range, and whether to use an approximate or exact match. In the example formula, A2 is the value to find; Catalog!$A$2:$C$100 is the range; 3 means the third column of that range; and FALSE requests an exact match.

The return-column number is counted from the left edge of the selected range, not from the worksheet’s column letters. In A:C, column A is 1, B is 2, and C is 3. If you instead selected B:D, column D would be number 3. The lookup value must be in the range’s first column, and VLOOKUP can return values only from columns to its right.

The exclamation mark separates a worksheet name from a cell or range reference. Dollar signs lock the range so it stays fixed when the formula is copied down. A2 has no dollar signs, so it can change to A3, A4, and so on as you fill the formula into further rows.

Worked example with a Catalog sheet

Suppose Orders!A2 contains the code P-104. On the Catalog sheet, A2:A100 contains product codes, B2:B100 contains descriptions, and C2:C100 contains prices. If the Catalog row for P-104 contains “Desk lamp” in column B and 12.50 in column C, the example formula returns 12.50. If you want the description instead, use 2 as the return-column number.

With the lookup code in Orders!A2 and product codes and descriptions in Catalog!A2:B100, use =VLOOKUP(A2,Catalog!$A$2:$B$100,2,FALSE). If P-104 is in the Catalog list, this returns Desk lamp. Notice that the selected range ends at B, because the requested result is in its second column.

For a basic two-sheet lookup, set up the data like this:

  • Keep the value to search for in a cell on the destination sheet, such as Orders!A2.
  • Make the lookup key the leftmost column in the source range on the other sheet.
  • Include the column containing the result in the selected range.
  • Use an exact match for identifiers, names, product codes, and most ordinary list lookups.

Entering and copying the formula

On the destination sheet, select the cell where the answer should appear, type the formula, and press Enter. In Excel for Microsoft 365 on Windows, you can then select the formula cell and drag its fill handle—the small square at the lower-right corner—down the column to apply it to other rows. The locked source range remains the same while the lookup reference changes by row.

You can also enter a sheet reference without typing the entire range: start the formula, click the source sheet tab, and select the cells. Excel inserts the sheet and range reference into the formula. This is useful when a tab name is long or contains punctuation. Check the final column number and ensure the first column of the selected range contains the lookup keys.

On a Mac, the formula syntax and worksheet-reference rules are the same. Excel for the web also supports VLOOKUP and the same basic formula structure. Ribbon layout and keyboard shortcuts can differ between Windows, Mac, and the browser, but you can enter the formula directly in the formula bar or a worksheet cell in each version.

Exact matches and approximate matches

For most lookups between sheets, use FALSE as the fourth argument. It tells Excel to find a value that matches the lookup value rather than returning a nearby value. This is important for product codes, employee IDs, invoice numbers, and names: a near match could produce a plausible but incorrect result.

TRUE requests an approximate match, which is useful for certain ordered bands, such as assigning a rate based on a threshold. For approximate matching, the first column of the lookup range needs to be sorted in ascending order. Excel may return the closest qualifying value rather than a cell with identical contents. Because leaving the fourth argument out can result in approximate-match behavior, explicitly writing FALSE makes the intention clear and avoids a frequent source of mistakes.

Exact matching does not mean Excel treats every visually similar value as identical. A number stored as text may fail to match the same digits stored as a number. Extra spaces, different punctuation, or inconsistent spelling can also prevent a match. When a value appears to be present but VLOOKUP does not find it, compare the underlying data types and contents rather than relying only on how the cells look.

Sheet names, spaces, and references

If a worksheet name has no spaces or special characters, Excel can use a reference such as Catalog!$A$2:$C$100. Names containing spaces or certain punctuation need single quotation marks around the sheet name. For example, with an order code in A2 and the source range on a sheet named Product List, use =VLOOKUP(A2,'Product List'!$A$2:$C$100,3,FALSE). Given the same data as the earlier example, this returns the matching price of 12.50.

Quotation marks around the sheet name are not the same as double quotation marks around text values. In a worksheet reference, the single quotes mark the sheet name; the exclamation mark still separates the name from the range. If you rename a sheet using Excel, Excel generally updates formulas that refer to it. If a formula was typed manually, check spelling and punctuation when you receive a sheet-reference error.

Common errors and how to fix them

  • #N/A: Excel did not find an exact match. Confirm the lookup value exists in the first column of the selected range, then check for leading or trailing spaces and text-versus-number differences.
  • #REF!: The return-column number is greater than the number of columns in the selected range, or a referenced column or sheet was removed. Recheck the range and the column index.
  • #VALUE!: The column index is invalid, commonly because it is less than 1. Use a positive number that identifies a column inside the selected range.
  • Wrong result: The formula may use an approximate match, the wrong return-column number, or a range that starts in the wrong column. Confirm the fourth argument is FALSE and count the result column from the left edge of the selected range.
  • Formula changes unexpectedly when copied: Lock the source range with dollar signs, for example $A$2:$C$100. Leave the lookup cell relative if each row should search for its own value.

If you want a friendly message instead of #N/A, you can wrap the lookup in IFERROR. With the lookup code in A2 and the source table in Catalog!A2:C100, use =IFERROR(VLOOKUP(A2,Catalog!$A$2:$C$100,3,FALSE),"Not found"). For a code absent from the Catalog range, the result is Not found; for a match, it returns the price from column C. While building or checking a lookup, it is often better to see the original error first, because IFERROR can also hide other problems in the formula.

Keeping the lookup manageable as data grows

A fixed reference such as $A$2:$C$100 is easy to understand, but it will not include new records entered below row 100. If the source list is likely to grow, consider converting it to an Excel table: select a cell in the data and choose Insert > Table, confirming that the table has headers if it does. Tables expand when new rows are added and make source data easier to manage. You can still use VLOOKUP with a table-based reference, although structured references have their own syntax and may be less familiar at first.

Keep the lookup column consistent and avoid blank rows or duplicate keys when possible. If the same lookup value appears more than once, VLOOKUP returns the first matching row it encounters in the selected range. It does not combine duplicate records or report every match. If duplicates are meaningful, decide which record should be returned or use a method designed to return multiple results.

VLOOKUP compared with alternatives

VLOOKUP is useful when the key is in the leftmost column of the range and the needed result is to its right. Its column number can become fragile if someone inserts or rearranges columns inside the range, because the formula’s number may then point to a different field. It also cannot return a value from a column to the left of the key.

In Microsoft 365 and Excel 2021 or later, XLOOKUP is a flexible alternative that separates the lookup range from the return range. With a lookup code in A2, codes in Catalog!A2:A100, and prices in Catalog!C2:C100, use =XLOOKUP(A2,Catalog!$A$2:$A$100,Catalog!$C$2:$C$100,"Not found"). For P-104, this returns 12.50; if the code is missing, it returns Not found. XLOOKUP can search in either direction and does not require a numeric return-column index. Older Excel versions may not support it, so VLOOKUP remains useful for compatibility.

INDEX and MATCH are another option in Excel versions that support those functions. They can look to either side of the key and can be useful in existing workbooks, but their combined formula is less direct for a new user than VLOOKUP or XLOOKUP. If the key is already the leftmost field and the result is to its right, VLOOKUP is often a straightforward choice.

Frequently Asked Questions

Can VLOOKUP search a different sheet in the same workbook?
Yes. Put the sheet name, an exclamation mark, and the source range in the second argument. Use single quotation marks around sheet names with spaces, such as ‘Product List’!A2:C100.

Does VLOOKUP work between two different Excel files?
Yes, it can reference a range in another workbook, but the formula then includes an external workbook reference. The source file may need to be accessible, and moving or renaming it can affect the link. For a simple task, keeping the source and destination sheets in one workbook is easier to maintain.

Why does VLOOKUP return #N/A when the value is on the other sheet?
Check that the lookup value is in the first column of the selected range and that both values have the same underlying type. Extra spaces, text-versus-number differences, or an incomplete range can make two values that look alike fail to match.

Can VLOOKUP return a value from a column to the left?
No. The lookup column must be the first column in the selected range, and VLOOKUP returns a value from a column to its right. XLOOKUP or INDEX and MATCH can handle a return column on either side.

What happens when the lookup value appears more than once?
VLOOKUP returns the first matching row in the selected range. It does not return all duplicate matches. Check whether the key should be unique, or use a different approach if you need multiple results.

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.