How to Use VLOOKUP Across Sheets in Excel

To use VLOOKUP across sheets, put the source sheet name before the lookup range. The pattern is:

=VLOOKUP(lookup_value,SheetName!lookup_range,column_number,FALSE)

For example, if your order sheet has a product ID in A2 and your product list is on a sheet named Products, this formula returns the product name:

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

VLOOKUP works the same way whether the lookup table is on the current worksheet or another worksheet. The main difference is the sheet reference before the range.

1. Basic Cross-Sheet VLOOKUP Formula

Use this structure when the lookup table is on another sheet:

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

Here is what each part means:

The lookup column must still be the first column in the table array. In the example above, Excel searches column A on the Products sheet and returns a value from the third column of Products!$A$2:$D$100.

2. Example Workbook Setup

Imagine a workbook with two sheets:

On the Orders sheet:

On the Products sheet:

To return the product name into Orders!C2, use:

=VLOOKUP(B2,Products!$A$2:$D$100,2,FALSE)

To return the price into Orders!D2, use:

=VLOOKUP(B2,Products!$A$2:$D$100,4,FALSE)

After the first formula works, fill it down the order list.

3. Use Quotes When Sheet Names Have Spaces

If the sheet name contains spaces, punctuation, or special characters, wrap the sheet name in single quotes:

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

This is one of the most common cross-sheet formula mistakes.

You do not have to type the sheet reference manually. Start the VLOOKUP formula, then click the other sheet tab and select the lookup range. Excel will insert the sheet reference for you.

4. Lock the Lookup Range Before Filling Down

When you copy a VLOOKUP formula down many rows, lock the lookup table with dollar signs:

=VLOOKUP(B2,Products!$A$2:$D$100,2,FALSE)

If you use a relative range like Products!A2:D100, the reference can shift as you copy the formula:

=VLOOKUP(B2,Products!A2:D100,2,FALSE)

That can make later rows search the wrong part of the product table.

If the dollar signs feel confusing, review Excel cell references, especially absolute references and the F4 shortcut.

5. Make Sure the Table Array Starts with the Lookup Column

Cross-sheet VLOOKUP still follows the normal VLOOKUP rule: the first column of the table array must contain the value you are searching for.

This works if product IDs are in column A on the Products sheet:

=VLOOKUP(B2,Products!$A$2:$D$100,4,FALSE)

This does not work if the Products sheet starts with another column, such as Category, and product IDs are actually in column B:

=VLOOKUP(B2,Products!$A$2:$D$100,4,FALSE)

That formula searches the Category column, not the Product ID column. In that case, start the range at column B:

=VLOOKUP(B2,Products!$B$2:$D$100,3,FALSE)

If you need to search a column and return a value from a column to the left, VLOOKUP is the wrong tool. Use XLOOKUP or INDEX MATCH instead.

6. Fix #N/A in Cross-Sheet VLOOKUP

When VLOOKUP across sheets returns #N/A, the most likely causes are the same as a normal VLOOKUP, but they are easier to miss because the source data is on another tab.

If missing values are expected, wrap the formula with IFNA after checking the lookup logic:

=IFNA(VLOOKUP(B2,Products!$A$2:$D$100,2,FALSE),"Not found")

For a deeper checklist, use How to Fix VLOOKUP #N/A in Excel.

7. Use Excel Tables for Safer Cross-Sheet Lookups

Fixed ranges work, but Excel Tables are safer when the source list grows. Convert the source range on the Products sheet to a table with Ctrl+T, then give it a clear name such as ProductsTable.

Then your formula can use the table name:

=VLOOKUP(B2,ProductsTable,2,FALSE)

This has two advantages:

If you use many lookup formulas, named tables usually make the workbook easier to audit than long worksheet ranges.

8. VLOOKUP Across Sheets vs Across Workbooks

Looking up from another worksheet in the same workbook is usually reliable. Looking up from another workbook works too, but it is more fragile because the external file path can change.

Example external workbook reference:

=VLOOKUP(A2,'[Price List.xlsx]Products'!$A$2:$D$100,4,FALSE)

Use cross-workbook VLOOKUP only when the source file is stable and shared with everyone who needs the formula. If the source workbook is moved, renamed, or not available, the formula can break or ask users to update links.

For most small business spreadsheets, it is simpler to keep the lookup table in the same workbook on a separate sheet.

9. When XLOOKUP Is Cleaner

If you use Microsoft 365 or Excel 2021 or later, XLOOKUP is usually cleaner for new workbooks:

=XLOOKUP(B2,Products!$A$2:$A$100,Products!$B$2:$B$100,"Not found")

Compared with VLOOKUP, XLOOKUP:

Use VLOOKUP when you need compatibility with older Excel versions or inherited workbooks. Use XLOOKUP when you are building a new workbook in modern Excel.

For a broader decision guide, see VLOOKUP vs XLOOKUP vs INDEX MATCH.

10. Practice Example

Use this setup:

On the Sales sheet:

On the Product List sheet:

Tasks:

Reference answers:

11. Summary

To use VLOOKUP across sheets, reference the lookup table with SheetName!range, lock the source range before filling down, and use quotes around sheet names with spaces. The lookup column must still be the first column in the table array. If the formula returns #N/A, check the source sheet for missing values, extra spaces, mismatched data types, and shifted ranges.

12. Related Guides

• Use the VLOOKUP troubleshooting guide when the formula returns the wrong result or a different error.
• Fix missing matches with How to Fix VLOOKUP #N/A in Excel.
• Combine sheet references with VLOOKUP with Multiple Criteria in Excel when one lookup value is not unique.
• Review Excel cell references before copying lookup formulas down a long list.
• Decide when a lookup should become a dynamic report with Excel FILTER vs VLOOKUP.
• Compare modern alternatives in Excel XLOOKUP tutorial.
• Choose the best lookup formula with VLOOKUP vs XLOOKUP vs INDEX MATCH.