Look Up a Value on Another Sheet

Excel Formulas › Lookup

All versionsVLOOKUPINDIRECT

Looking up data that lives on a different sheet — prices on a “Products” tab, rates on a “Rates” tab — is just a normal lookup with the sheet name in the reference. To pick the sheet dynamically from a cell, add INDIRECT.

Quick formula: to look up E2 in a table on the Products sheet:
=VLOOKUP(E2, Products!A:B, 2, FALSE)
Prefix the range with the sheet name and an exclamation mark: Products!A:B. The lookup works exactly as if the data were local.

Functions used (tap for the full reference guide):

VLOOKUPINDIRECTXLOOKUP

The example

The lookup table sits on a Products tab; the formula on another sheet pulls from it.

AB
1Products! tab
2Widget$10
3Gadget$20
4Lookup “Gadget”:$20

The formula

From any other sheet:

=VLOOKUP("Gadget", Products!A2:B3, 2, FALSE) // reads the Products tab → $20

How it works

The sheet name is the only addition:

  1. Reference the other sheet by name, then !, then the range: Products!A:B.
  2. Everything else is a normal VLOOKUP/XLOOKUP — the function doesn’t care which sheet the data is on.
  3. If the sheet name has spaces, wrap it in single quotes: 'Price List'!A:B.
  4. To choose the sheet from a cell (e.g. a month tab named in D1), build the reference with INDIRECT (see variations).

Dynamic sheet from a cell: =VLOOKUP(E2, INDIRECT(D1 & "!A:B"), 2, FALSE) looks up on whatever sheet D1 names — switch “Jan” to “Feb” and the formula reads the other tab.

Try it: interactive demo

Live demo

Pick the source sheet and the product.

Price:

Variations

XLOOKUP across sheets

Same idea, modern function:

=XLOOKUP(E2, Products!A:A, Products!B:B)

Dynamic sheet with INDIRECT

Sheet name from a cell:

=VLOOKUP(E2, INDIRECT(D1 & "!A:B"), 2, FALSE)

Sheet name with spaces

Quote the name:

=VLOOKUP(E2, 'Price List'!A:B, 2, FALSE)

Pitfalls & errors

#REF! with INDIRECT and a wrong name. If the cell names a sheet that doesn’t exist (typo, renamed tab), INDIRECT errors. Keep the sheet name in the cell exact.

INDIRECT doesn’t work across closed workbooks. It only resolves references in open files. For other workbooks, link directly or use Power Query.

Quote names with spaces or symbols. 'Q1 Data'!A:B — forget the quotes and you get an error.

Practice workbook

📊
Download the free Look Up a Value on Another Sheet practice workbook
A Jan and Feb data sheet with a live cross-sheet VLOOKUP and the INDIRECT dynamic-sheet version, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I look up data from another sheet in Excel?
Reference the sheet in the range: =VLOOKUP(E2, Products!A:B, 2, FALSE). The sheet name plus ! tells Excel where the table is; the lookup works as normal.
How do I choose the source sheet from a cell?
Use INDIRECT to build the reference: =VLOOKUP(E2, INDIRECT(D1 & "!A:B"), 2, FALSE) looks up on whatever sheet D1 names.
How do I reference a sheet name with spaces?
Wrap the sheet name in single quotes: ='Price List'!A:B. Without the quotes, Excel returns an error.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Build references with INDIRECT · Sum across sheets (3D) · Two-way lookup

Function references: VLOOKUP · INDIRECT · XLOOKUP