Lookup formula page
VLOOKUP From Another Sheet
Use this when an older workbook needs a cross-sheet lookup and the lookup column is first in the source table.
Use VLOOKUP across worksheet tabs.
If A2 is B-201, the formula returns the fourth column from Products!A:D.
Copy formulas
=VLOOKUP(A2, Products!A:D, 4, FALSE)=VLOOKUP(A2, Products!A:D, 4, FALSE)Excel and Google Sheets use the same sheet-reference syntax for VLOOKUP. A tab name with spaces must be enclosed in single quotes in both apps.
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 B-201, the formula returns the fourth column from Products!A:D.
How the formula works
- Products!A:D points to the table on the Products tab.
- A2 is searched in the first column of that table.
- FALSE keeps the lookup exact.
| Syntax piece | Role in the formula |
|---|---|
| A2 | The SKU to find. |
| Products!A:D | The four-column lookup table on the Products tab. |
| 4 | The return column number counted from the first column of the table. |
| FALSE | The exact-match setting. |
Verified examples
=VLOOKUP(A2, Products!A:D, 4, FALSE)Excel: Put the sample product table on Products and enter B-201 in A2. Returns: 7
=VLOOKUP(A2, 'Product List'!A:D, 4, FALSE)Google Sheets: Put the table on Product List and enter A-100 in A2. Returns: 49
=IFERROR(VLOOKUP(A2, Products!A:D, 4, FALSE), "Not found")Excel: Enter C-999 in A2; it is absent from Products. Returns: Not found
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| The formula returns #REF! after a tab change | The referenced sheet was renamed or deleted. | Restore the tab or update the reference to the current sheet name. |
| A valid SKU cannot be found | VLOOKUP only searches the first column of Products!A:D. | Put the SKU in the first table column or use XLOOKUP or INDEX MATCH. |
| The formula returns #REF! for the column index | The index is wider than the selected table. | Use 1 through 4 for Products!A:D, or widen the table reference. |
When not to use this formula
- Do not use VLOOKUP when the key is not the first column of the cross-sheet table or when a left lookup is required.
Alternatives
| Alternative | When to use it |
|---|---|
| Lookup Value From Another Sheet | Use when modern XLOOKUP should return from an independently selected column. |
| VLOOKUP Formula Builder | Use when the sheet, table range, and return index need guided setup. |
Related formulas
Official references
- VLOOKUP function from Microsoft
FAQ
Why are quotes needed around Product List?
Sheet names containing spaces must be wrapped in single quotes, as in 'Product List'!A:D.
Does this reference another spreadsheet file?
No. Products!A:D references another tab in the same workbook or Google Sheets file.