How Sheet References Work in VLOOKUP
A standard VLOOKUP looks like: =VLOOKUP(A2, D:E, 2, FALSE). To reference a table on a different sheet, replace the range with SheetName!Range:
VLOOKUP from Sheet2 — basic syntax
=VLOOKUP(A2, Sheet2!A:B, 2, FALSE)
If the sheet name contains spaces or special characters, wrap it in single quotes: 'Sales Data'!A:B
Step-by-Step: VLOOKUP From Sheet2 to Sheet1
- Click on the cell in Sheet1 where you want the result
- Type
=VLOOKUP( - Click on your lookup value cell (e.g. A2) — this is what you are searching for
- Type a comma, then click on the Sheet2 tab at the bottom
- Select the entire lookup table range (e.g. A:B) — Excel adds the sheet reference automatically
- Type a comma, then the column number to return (e.g. 2 for the second column)
- Type
,FALSE)for exact match and press Enter
Add IFERROR to handle missing values:
=IFERROR(VLOOKUP(A2,Sheet2!A:B,2,FALSE),"Not found") — shows "Not found" instead of #N/A when the value does not exist in Sheet2.VLOOKUP From a Different Workbook
Cross-workbook VLOOKUP — workbook open
=VLOOKUP(A2, [Products.xlsx]Sheet1!$A:$B, 2, FALSE)
When the source workbook is closed, Excel converts the reference to include the full file path automatically. Cross-workbook formulas update when you open the file — Excel will prompt you to "Update Links".
The Modern Alternative: XLOOKUP Across Sheets
For Excel 365 users, XLOOKUP is cleaner for cross-sheet lookups — especially for left lookups or multiple columns:
XLOOKUP from Sheet2
=XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B, "Not found")
Common Cross-Sheet VLOOKUP Errors
- #REF! error: Sheet name is misspelled or the sheet has been deleted. Double-check the sheet tab name
- #N/A error: Value exists on Sheet2 but with different formatting (text vs number). Use TRIM() and VALUE() to normalise
- Wrong results after adding columns: col_index_num is based on position — if you add columns to Sheet2, update the number. Better: use INDEX MATCH or XLOOKUP which reference columns directly
FAQ
Frequently Asked Questions
How do I reference another sheet in a VLOOKUP formula?▾
Include the sheet name followed by an exclamation mark before the range: =VLOOKUP(A2, Sheet2!A:B, 2, FALSE). If the sheet name has spaces, use single quotes: =VLOOKUP(A2, 'Sales Data'!A:B, 2, FALSE). You can also click on the sheet tab while building the formula and Excel adds the reference automatically.
Can VLOOKUP pull data from a completely different Excel file?▾
Yes — include the workbook name in square brackets: =VLOOKUP(A2, [Products.xlsx]Sheet1!$A:$B, 2, FALSE). When the source file is closed, the full path is shown. Cross-workbook links update when the source file is opened.
Why does my cross-sheet VLOOKUP return #N/A when the value exists?▾
Most common cause: the value in the lookup column is formatted differently across sheets (number in one, text in another). Wrap the lookup value with TRIM() and VALUE() to normalise formatting. Also check for hidden spaces.
What is the easiest way to generate a cross-sheet VLOOKUP formula?▾
Use ExcelForm at excelform.xyz — describe your lookup in plain English, for example: 'fetch the price from Sheet2 column B where the product ID in Sheet2 column A matches A2'. ExcelForm generates the complete formula with the correct sheet reference syntax instantly.