Lookup formula page
Lookup Value From Another Sheet
Use this when the lookup table lives on a different tab such as Products or Prices.
Look up a value from another sheet tab.
If A2 is A-100, the formula returns the price from the Products tab.
Copy formulas
=XLOOKUP(A2, Products!A:A, Products!D:D, "Not found", 0)=XLOOKUP(A2, Products!A:A, Products!D:D, "Not found", 0)XLOOKUP can reference another tab in the same workbook in Excel and Google Sheets; a separate Google spreadsheet requires an external import or linked range first.
Example data
| SKU | Item | Category | Price | Stock |
|---|---|---|---|---|
| A-100 | Keyboard | Hardware | 49 | 18 |
| A-101 | Mouse | Hardware | 25 | 32 |
| B-200 | Desk Mat | Office | 18 | 9 |
| B-201 | Notebook | Office | 7 | 64 |
If A2 is A-100, the formula returns the price from the Products tab.
How the formula works
- Products!A:A searches the SKU column on another sheet.
- Products!D:D returns the aligned price.
- The formula keeps the current sheet clean while referencing the source tab.
| Syntax piece | Role in the formula |
|---|---|
| A2 | The key on the current sheet to look up. |
| Products!A:A | The key column on the Products tab. |
| Products!D:D | The return column on the Products tab. |
| Not found and 0 | The fallback text and exact-match mode. |
Verified examples
=XLOOKUP(A2, Products!A:A, Products!D:D, "Not found", 0)Excel: Enter A-100 in A2 and place the sample product keys on Products!A:A with prices on Products!D:D. Returns: 49
=XLOOKUP(A2, Products!A:A, Products!D:D, "Not found", 0)Google Sheets: Enter B-201 in A2 and use the Products tab sample. Returns: 7
=XLOOKUP(A2,'Product List'!A:A,'Product List'!D:D,"Not found",0)Google Sheets: Enter A-101 in A2 and store the same product table on a tab named Product List. Returns: 25
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| The tab reference returns #REF! | The Products tab was renamed, deleted, or is unavailable. | Confirm the tab name and rebuild the reference from the workbook's current sheet name. |
| A tab name with spaces fails | The sheet name is not wrapped in single quotes. | Use a reference such as 'Product List'!A:A for names containing spaces. |
| The lookup does not work across another spreadsheet | A tab reference only addresses a tab in the same workbook or spreadsheet file. | Connect the other file with the platform's import or linked-range feature, then look up the imported range. |
When not to use this formula
- Do not treat Products!A:A as a cross-file reference; it only addresses a tab in the current workbook or spreadsheet.
Alternatives
| Alternative | When to use it |
|---|---|
| VLOOKUP From Another Sheet | Use when the key is the first column of the referenced table and VLOOKUP is required. |
| XLOOKUP Formula Builder | Use when the tab ranges, fallback, and match mode need guided setup. |
Related formulas
Official references
- XLOOKUP function from Microsoft
FAQ
Does this reference another spreadsheet file?
No. Products!A:A references another tab in the current file. A different file must first be connected or imported.
When are quotes required around a sheet name?
Use single quotes when the tab name contains spaces or characters that require quoting, such as 'Product List'.