VLOOKUP Between Two Sheets in Excel: A Practical Guide

Formulas & functions · Updated November 18, 2026

To look up a value from one Excel sheet on another, enter a VLOOKUP formula such as =VLOOKUP(A2,Products!$A$2:$C$100,3,FALSE). This example searches for the value in A2 in the first column of the Products sheet’s range and returns the matching value from its third column, using an exact match.

What VLOOKUP between sheets means

VLOOKUP searches down the first column of a selected table range for a value, then returns a value from another column in the same row. When the table is on a different worksheet, the formula includes that sheet’s name as part of the range reference. The lookup can be on another tab in the same workbook; it does not require copying the source data onto the current sheet.

For a concrete example, suppose an Orders sheet has product codes in column A and you want to fill in product prices in column B. The Products sheet has product codes in column A, product names in column B, and prices in column C. If the code in Orders!A2 is P-104 and the matching row on Products lists a price of 19.95, the example formula returns 19.95. Those values are illustrative; your result depends on the contents of your workbook.

VLOOKUP does not search every column in the range. Its lookup value must be found in the range’s leftmost column, and the return column must be to the right. That left-to-right limitation is important when choosing the range and deciding whether VLOOKUP is the right tool.

Build the formula step by step

Start by identifying the cell to look up, the source sheet, and the source columns. In the example above, A2 is the product code to find, and the Products sheet holds the lookup table. The range A:C starts with the codes and includes the price column.

  1. Click the destination cell on the sheet where you want the result, such as Orders!B2.
  2. Type =VLOOKUP(, then select or type the cell containing the value to find. Here, that is A2.
  3. Enter a comma, then specify the table range on the other sheet. Use the first column containing the lookup values as the range’s first column.
  4. Enter the number of the return column within that range. For A:C, the first column is 1, the second is 2, and the third is 3.
  5. Enter FALSE for an exact match, close the parenthesis, and press Enter.
  6. Copy the formula down for additional rows. Lock the source range with dollar signs so it does not shift as you fill down.

For the example, the table range is fixed as $A$2:$C$100. On Windows, while editing a selected cell reference in a formula, press F4 to cycle through relative and absolute reference styles. On a Mac, Command+T cycles reference styles in Excel; keyboard settings can affect function-key behavior. You can also type the dollar signs yourself.

Understand each part of VLOOKUP

The function has four arguments: the value to find, the table range, the index number of the return column, and the match mode. In =VLOOKUP(A2,Products!$A$2:$C$100,3,FALSE), A2 is the lookup value; Products!$A$2:$C$100 is the table; 3 means return the third column of that selected range; and FALSE requests an exact match.

The column number is counted from the left edge of the range, not from the worksheet’s column A. If your selected range is D:F, D is index 1, E is index 2, and F is index 3. This is a frequent source of incorrect results when a range begins somewhere other than column A.

If the sheet name contains spaces or punctuation, enclose it in single quotation marks in the reference. With a source table on a sheet named Product List, the equivalent formula is =VLOOKUP(A2,'Product List'!$A$2:$C$100,3,FALSE). A sheet name without spaces, such as Products, does not need those quotation marks. Excel may add the sheet reference automatically if you select the range with your mouse while building the formula.

Make the formula reliable when copying it

When you fill a formula down a column, the lookup cell should normally change from A2 to A3, A4, and so on. The source table should remain fixed. That is why the example uses the relative reference A2 but absolute references for the table, $A$2:$C$100. Without the dollar signs, the table range may move as the formula is copied and can eventually omit the row containing a match.

For a small or stable table, a fixed range is simple. If the source list will grow, consider turning it into an Excel Table: select a cell in the data, choose Insert > Table, and confirm whether the data has headers. Tables expand when new rows are added and can make formulas easier to maintain. You can also select a generously sized range, but including many blank rows is less tidy and does not solve every data-management issue.

Use matching data types on both sheets. A code stored as the number 104 may not match a code stored as text, even if both appear as 104 on screen. Leading or trailing spaces can also prevent an exact match. Check the underlying values, not just their display formatting, and clean inconsistent source data where appropriate. Keep leading zeros in product or account codes if they are meaningful; converting such codes to numbers can remove them.

Exact match, approximate match, and duplicates

For codes, names, IDs, and most ordinary cross-sheet lookups, use FALSE. In Excel, FALSE requests an exact match; TRUE or an omitted fourth argument requests approximate matching. Approximate matching is intended for ordered ranges such as grade boundaries or rate bands, where the lookup column is sorted in ascending order and the closest lower value is appropriate. Using the default accidentally on an unsorted list can return an unexpected result.

An exact match does not mean VLOOKUP checks every duplicate and lets you choose. If the lookup column contains the same value more than once, VLOOKUP returns the first matching row it encounters from the top of the range. Remove or resolve duplicates if they represent bad data, or use a different approach if you need to return multiple matching records.

Exact matching can also work with text and numbers, but it does not ignore differences in the stored values. For example, an extra space can make two text entries different. If you are matching names, remember that names may not be unique: an order or customer ID is often a safer lookup key than a person’s name.

Common errors and how to fix them

  • #N/A: Excel did not find an exact match. Check for spelling differences, spaces, mismatched text-versus-number values, or a lookup value missing from the source list. Confirm that the lookup column is the first column of the selected range.
  • #REF!: The column index is larger than the number of columns in the selected range, or a referenced column or sheet was deleted. Recount the return column from the range’s left edge.
  • Wrong value: Check that the fourth argument is FALSE and that the index points to the intended return column. Also check for duplicate keys, because the first duplicate is returned.
  • Formula appears as text: The cell may be formatted as Text, or the entry may start with an apostrophe. Set the cell to General, then re-enter the formula.
  • Formula separator error: Some regional Excel settings use semicolons instead of commas between arguments. If Excel rejects a valid comma-separated formula, check the list separator used by your installation and adapt the separators to that setting.

To show a friendlier message when no match exists, you can wrap the lookup in IFNA. With Orders!A2 holding the code and Products!A2:C100 holding the source table, this formula returns “Not found” when VLOOKUP returns #N/A; for a match such as P-104 with a price of 19.95, it returns 19.95: =IFNA(VLOOKUP(A2,Products!$A$2:$C$100,3,FALSE),"Not found"). IFNA handles the not-found error specifically, while a broad error handler can conceal unrelated problems such as an invalid column index.

VLOOKUP versus XLOOKUP and other options

VLOOKUP is useful when the lookup column is on the left of the value you want to return and you want a familiar, widely compatible formula. It also works in older Excel versions. Its main structural weakness is that it uses a numeric column index and cannot return values from a column to the left of the lookup column.

In Microsoft 365 and Excel 2021 or later, XLOOKUP is often more flexible. It takes separate lookup and return ranges, uses exact matching by default, and can search in either direction. With the same illustrative layout—code in Orders!A2, codes in Products!A2:A100, and prices in Products!C2:C100—the formula =XLOOKUP(A2,Products!$A$2:$A$100,Products!$C$2:$C$100,"Not found") returns the corresponding price or “Not found.” XLOOKUP is not available in some older Excel releases, so use VLOOKUP or another compatible method when a workbook must support those versions.

INDEX and MATCH can also perform a lookup when the return column is to the left, and they are supported in many older Excel versions. They take more explanation than VLOOKUP, but their separate lookup and return references avoid VLOOKUP’s leftmost-column requirement. If you only need a single result and the lookup table is organized left to right, VLOOKUP remains a reasonable choice.

Frequently Asked Questions

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

Does VLOOKUP work between two different Excel files?
Yes, if the formula references the other workbook’s range. Excel generally creates the external reference when you select cells in the open source workbook. External links can become difficult to maintain if the source file is moved, renamed, or unavailable, so a second worksheet in the same workbook is simpler when practical.

Why does my VLOOKUP return #N/A when the value is visible?
The displayed values may differ underneath: one may be text and the other a number, or one may contain an extra space or a different character. Also make sure the lookup value is in the first column of the range and that FALSE is the fourth argument.

Can VLOOKUP return a value from a column to the left?
No. The lookup column must be the first column in the selected table range, and VLOOKUP returns a column to its right. Reorder the data, use INDEX and MATCH, or use XLOOKUP in Microsoft 365 or Excel 2021 and later.

How do I apply the formula to every row?
After entering the formula in the first result cell, use the fill handle at the cell’s lower-right corner to copy it down, or copy and paste it into the destination cells. Lock the source range with dollar signs and leave the row-specific lookup reference relative.

Choose the right lookup for the job

For a straightforward match across two sheets, begin with an exact-match VLOOKUP and verify the range, lookup column, and return-column number. Then test it against a value you can check manually before filling it down. A dependable lookup depends as much on clean, consistently typed keys and a well-structured source table as on the formula itself. If your layout requires looking left, returning several matches, or adapting frequently to changing columns, choose XLOOKUP or another lookup method suited to that structure.

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.