VLOOKUP Across Sheets in Excel: Formula and Tips

Yes. If the value to look up is in A2 on an Orders sheet, and a Product List sheet has lookup codes in column A and prices in column C, enter =VLOOKUP(A2,'Product List'!$A$2:$C$100,3,FALSE) on Orders. This searches the other sheet for the code and returns its price from column C.

What VLOOKUP across sheets means

VLOOKUP can search a table on a different worksheet in the same workbook. The formula is entered on the sheet where you want the answer to appear, while its table range points to the sheet containing the source data. The lookup value can also be on the current sheet, as in the example above.

The worksheet name and an exclamation mark identify the source sheet. In the example, 'Product List'! points to the Product List tab. The apostrophes around the name are needed because it contains a space. If the tab were named Products, the reference could be written without them as Products!A2:C100. Using apostrophes consistently is also acceptable.

Both sheets need to be in the same workbook for this simple reference. The formula does not copy or move the source data; it displays a result based on the value it finds in the source range. If the source value changes, Excel normally recalculates the formula and updates the result.

How the formula works

VLOOKUP uses four arguments in this order: the value to search for, the table range, the number of the return column within that range, and whether to use an exact or approximate match. In the opening example, A2 is the lookup value, the range on Product List is the table, 3 means the third column of that selected range, and FALSE requests an exact match.

The column number is counted from the left edge of the selected range, not from the worksheet’s column letters. For a range starting at A, column A is 1, B is 2, and C is 3. If the selected range instead starts at B, then B is 1 and D is 3. This distinction is a frequent source of unexpected results.

VLOOKUP searches only the leftmost column of its table range. The value it returns must be in a column to the right of that search column. For example, if product codes are in column A and prices are in column C, the range must include A through C. A range that begins at B cannot find codes in A, and VLOOKUP cannot return a value from a column to the left of its lookup column.

Set it up step by step

Open the worksheet where you want the result and select its result cell, such as B2 on Orders.
Start the formula with the cell containing the lookup value, such as A2.
Type a comma, then select the source worksheet tab and drag across the table columns and rows you need. Excel inserts a worksheet reference into the formula.
Type a comma and enter the number of the return column within that selected range.
Type a comma and enter FALSE for an exact match, then close the parenthesis and press Enter.
Check the returned value against the source row. If you will copy the formula down, lock the table range with dollar signs so it does not shift.

For the opening example, the dollar signs in $A$2:$C$100 keep the source range fixed when the formula is filled down. The reference A2 remains relative, so it can become A3, A4, and so on for subsequent order rows. In Microsoft Excel for Microsoft 365 on Windows, you can fill down by dragging the small square at the lower-right corner of the selected formula cell. Excel for Mac and Excel for the web use the same formula and offer the fill handle as well; interface details can vary slightly.

Worked example with sample data

Suppose Product List contains product codes, names, and prices in columns A, B, and C. Its first data rows are P-104, Desk Lamp, 24.99; P-208, Notebook Set, 8.50; and P-315, USB-C Cable, 12.00. On Orders, A2 contains P-104. The formula in the opening paragraph searches the first column of the source range, finds P-104, and returns the value in the third column: 24.99. It does not return the second column, because the requested column index is 3.

If you need the product name instead, the lookup range and lookup value can stay the same, but the return-column number would be 2. If Orders has P-208 in A3 and you fill the price formula down to B3, the relative lookup reference changes to A3 while the locked source range stays fixed. The result is 8.50. This is useful when a transaction sheet needs to display details maintained in a separate reference table.

Keep the lookup values and source values compatible. A code stored as text may not match a visually identical value stored as a number. Leading or trailing spaces can also prevent a match. Formatting changes how a value looks, but does not always change the underlying value or its type.

Sheet names and reference details

Sheet names with spaces, punctuation, or certain other special characters should be enclosed in single quotes in a worksheet reference. For example, the name Sales Data is referenced as 'Sales Data'!A2:C100. A sheet name without spaces, such as Products, can be referenced as Products!A2:C100. The exclamation mark separates the sheet name from the cell or range reference.

When building a formula by clicking cells on another sheet, Excel usually adds the needed sheet reference automatically. If you rename a worksheet within the same workbook, Excel generally updates references to that sheet. If you type a sheet name manually, check its spelling and spaces; an incorrect name can cause a reference error. A deleted or moved source range can also break a formula.

To look up data in a different workbook, the reference needs to identify that external workbook as well as the worksheet and range. Such links can depend on the source file’s location and availability, so they are less convenient than references within one workbook. If you only mean a different tab in the same file, you do not need an external-workbook reference.

Exact match, approximate match, and missing values

For identifiers such as product codes, employee IDs, invoice numbers, or names, use FALSE for an exact match. This tells Excel to return a result only when it finds a matching lookup value. If you omit the fourth argument, VLOOKUP uses approximate matching by default. Approximate matching is intended for sorted ranges and threshold tables, such as tax bands or commission rates; it is usually not the right choice for a list of unique codes.

If an exact value is not found, VLOOKUP returns #N/A. Check for spelling differences, extra spaces, a number-versus-text mismatch, or a lookup value missing from the source list. Also confirm that the formula is searching the correct first column. Avoid changing to approximate matching just to make #N/A disappear: it can return a plausible but incorrect result when the data is not arranged for that purpose.

Before relying on a lookup in a large workbook, test a few known values, including one that should not be present. That confirms both that the formula returns the intended column and that missing entries behave as expected. If blank or error results need special handling, add that only after the underlying lookup is correct; otherwise, an error-suppression formula can hide a faulty range or mismatched data.

Common problems and fixes

#N/A: The lookup value may not exist in the first column of the selected range, or it may differ because of spaces or data type. Compare the actual source values and confirm that the correct key column is first.
#REF!: The return-column number may exceed the number of columns in the selected range, or a referenced sheet or range may have been deleted. Count the columns from the left edge of the range.
Wrong result: Check whether the formula uses TRUE or omits the match argument. For ordinary code lookups, choose FALSE. Then verify the return-column index and make sure duplicate keys are not causing an earlier row to be returned.
Formula changes when filled down: Lock the source table range with dollar signs, for example $A$2:$C$100, while leaving the row reference for the lookup cell relative when each row should use a different key.
Formula appears as text: The result cell may be formatted as Text, or the entry may begin with an apostrophe. Change the cell format to General, then re-enter the formula.

VLOOKUP returns the first matching row it encounters. If the source table contains duplicate codes, later duplicates do not replace the first match. Removing unintended duplicates or choosing a genuinely unique lookup key makes results more dependable.

VLOOKUP alternatives and when to use them

VLOOKUP remains useful when the search key is in the leftmost column and the return value is somewhere to its right. It is familiar, works across worksheets, and is available in older Excel releases as well as Microsoft 365. Its main structural limitation is that it relies on a numbered return column and cannot look to the left.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It separates the lookup range from the return range, so it can return data from either side of the key and does not need a column index. With the same sample layout—P-104 in Orders A2, product codes in Product List A2:A100, and prices in Product List C2:C100—this formula returns 24.99 when the code is found and Not found otherwise: =XLOOKUP(A2,'Product List'!$A$2:$A$100,'Product List'!$C$2:$C$100,"Not found").

INDEX and MATCH are another established option when a lookup needs more flexibility or must support older Excel versions that do not include XLOOKUP. They can search one range and return from another, though the combined formula is less straightforward for some readers. For Microsoft 365 or Excel 2021, XLOOKUP is often simpler for a new formula. Choosing an alternative does not require moving the data to the same sheet.

Frequently Asked Questions

Can VLOOKUP look at another sheet in the same workbook?
Yes. Include the other worksheet’s name, an exclamation mark, and the source range in the table argument. Use single quotes around a sheet name that contains spaces.

Does VLOOKUP work between two different Excel files?
Yes, with an external workbook reference that identifies the source file, worksheet, and range. The link may be affected if the source workbook is renamed, moved, or unavailable. A different worksheet in the same workbook needs only a sheet reference.

Why does my VLOOKUP return #N/A from another sheet?
Usually the lookup value is not an exact match in the first column of the selected range. Check for extra spaces, different data types, a misspelled value, or a range that starts in the wrong column. Use FALSE for an exact lookup.

Can VLOOKUP return a value to the left of the lookup column?
No. VLOOKUP searches the first column of its table range and returns from a column to the right. Use XLOOKUP in Microsoft 365 or Excel 2021 and later, or use INDEX and MATCH, when the return data is to the left.

Do I need to use FALSE in every VLOOKUP?
Not in every possible case, but it is the safe choice for exact identifiers and ordinary list lookups. Approximate matching has specific uses, such as sorted threshold tables, and should be selected deliberately rather than used as a fix for missing matches.

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.