How to Lookup Value from Another Sheet in Excel
Look up a value from another Excel sheet with XLOOKUP, VLOOKUP, or INDEX MATCH using correct sheet references and locked ranges.
Method 1: XLOOKUP across sheets
Suppose the current sheet contains a product ID in A2 and a sheet named Products contains IDs in column A and prices in column C. Use =XLOOKUP(A2,Products!$A$2:$A$100,Products!$C$2:$C$100,"Not found"). XLOOKUP searches the first range and returns the aligned value from the second; exact match is the default. It is available in Microsoft 365 and Excel 2021 or later.
Build the reference by typing the function, switching to the other sheet, and selecting the range; Excel inserts the sheet qualifier for you. The qualifier is the sheet name followed by an exclamation mark. If the name contains a space or punctuation, wrap it in single quotes, as in 'Product List'!$A$2:$A$100; a quote inside a name is doubled. Dollar signs keep the arrays fixed when the formula is copied.
Method 2: VLOOKUP and INDEX with MATCH
For workbooks that must open in versions without XLOOKUP, use =VLOOKUP(A2,Products!$A$2:$C$100,3,FALSE). The key must be in the first column of the selected table, 3 is the column to return counted from the table’s left edge, and FALSE requests an exact match.
=INDEX(Products!$C$2:$C$100,MATCH(A2,Products!$A$2:$A$100,0)) separates the return range from the key range and works in every version. Both ranges must begin and end on aligned rows. The live exercise below uses one sheet because the practice grid is a single sheet; in Excel, adding the sheet qualifier to each source range is the only difference.
Method 3: Excel Tables and structured references
A fixed range such as A2:C100 silently excludes row 101. If the lookup data is an Excel Table named tblProducts, refer to its columns instead: =XLOOKUP(A2,tblProducts[ID],tblProducts[Price],"Not found"). Table names are workbook-wide, so this works from any sheet, needs no sheet qualifier, and expands when rows are appended.
INDIRECT can build a reference from text, such as =INDIRECT("'"&B1&"'!A2") to pick a sheet named in B1. Use it sparingly: it is volatile, it stops pointing at the right place when a sheet is renamed because the name lives in text, and it is hard to audit. A direct reference or a table is usually the better choice.
Keep the link healthy
Keep the lookup sheet in the same workbook when practical. A reference to another file looks like [Prices.xlsx]Sheet1!$A$2:$C$100 and needs that file to be reachable, with extra refresh and security prompts. Deleting the source sheet turns the formulas into #REF! errors, so rename sheets rather than recreate them.
If a cross-sheet lookup returns #N/A even though the key looks present, the cause is in the data, not the sheet reference: see why a VLOOKUP returns #N/A for spaces, text-versus-number keys, and approximate match.
Your turn
In F2, return the price for the ID in E2. In Excel, the A:C table could be on another sheet.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ID | Product | Price | Find ID | Price | |
| 2 | P-1 | Lamp | 18 | P-2 | ||
| 3 | P-2 | Chair | 24 | |||
| 4 | P-3 | Desk | 55 | |||
| 5 | P-4 | Shelf | 32 |
Answer: =XLOOKUP(E2,A2:A5,C2:C5,"Not found")
Worked example
Orders hold the IDs; the prices sit on a second sheet named “Product List”, so every reference needs quotes. P-9 is not in the list.
| A | |
|---|---|
| 1 | Product ID |
| 2 | P-2 |
| 3 | P-9 |
| A | B | C | |
|---|---|---|---|
| 1 | ID | Product | Price |
| 2 | P-1 | Lamp | 18 |
| 3 | P-2 | Chair | 24 |
| 4 | P-3 | Desk | 55 |
| Formula | Result |
|---|---|
=XLOOKUP(A2,'Product List'!$A$2:$A$4,'Product List'!$C$2:$C$4,"Not found") | 24 |
=XLOOKUP(A3,'Product List'!$A$2:$A$4,'Product List'!$C$2:$C$4,"Not found") | "Not found" |
=VLOOKUP(A2,'Product List'!$A$2:$C$4,3,FALSE) | 24 |
=INDEX('Product List'!$B$2:$B$4,MATCH(A2,'Product List'!$A$2:$A$4,0)) | "Chair" |
=SUM('Product List'!$C$2:$C$4) | 97 |
Quick mistake checklist
- Forgetting quotes around a sheet name containing spaces.
- Leaving source ranges relative before filling down.
- Using approximate
VLOOKUPfor IDs or codes.
Frequently asked questions
How do I reference a sheet with spaces?
Wrap its name in apostrophes, for example 'Product List'!A2:A100.
Can XLOOKUP return a value to the left?
Yes. Its lookup and return arrays are independent.
Why does the reference move when I copy the formula?
Lock the lookup arrays with dollar signs or use structured table references.
Can I look into another workbook?
Yes, but external links add path, access, refresh, and maintenance concerns. Keep the source workbook available and test link behavior.
Practice the skill
Continue with a checked FormulaCamp lesson: A Lookup Table on Another Sheet Free
Open the free lesson