How to Use VLOOKUP Across Sheets in Excel
Formula not working?
Share a small sample and the result you expected.
To use VLOOKUP across sheets, put the source sheet name before the lookup range. The pattern is:
=VLOOKUP(lookup_value,SheetName!lookup_range,column_number,FALSE)For example, if your order sheet has a product ID in A2 and your product list
is on a sheet named Products, this formula returns the product name:
=VLOOKUP(A2,Products!$A$2:$D$100,2,FALSE)VLOOKUP works the same way whether the lookup table is on the current worksheet or another worksheet. The main difference is the sheet reference before the range.
1. Basic Cross-Sheet VLOOKUP Formula
Use this structure when the lookup table is on another sheet:
=VLOOKUP(A2,Products!$A$2:$D$100,3,FALSE)Here is what each part means:
| Formula part | Meaning |
|---|---|
A2 |
The value you want to find on the current sheet |
Products!$A$2:$D$100 |
The lookup table on the Products sheet |
3 |
Return the value from the third column of that lookup table |
FALSE |
Require an exact match |
The lookup column must still be the first column in the table array. In the
example above, Excel searches column A on the Products sheet and returns a
value from the third column of Products!$A$2:$D$100.
VLOOKUP Between Two Sheets: The Short Version
If you only want the shortest working answer, this is it. With the order data on
the Orders sheet and the master list on Products, the formula on Orders is:
=VLOOKUP(B2,Products!$A$2:$D$100,2,FALSE)Read it as one sentence: find B2 on this sheet, search the first column of the
range on the Products sheet, return the second column of that range, and require
an exact match.
| Step | What to do |
|---|---|
| 1 | Start the formula on the sheet where the result should appear |
| 2 | Point at the lookup value on that same sheet |
| 3 | Switch to the source sheet and select the whole table, starting at the key column |
| 4 | Press F4 to lock the range before the formula is copied down |
| 5 | Count the return column from the key column, then finish with FALSE |
The one step you cannot skip is the third: the selected range has to start on the column you are searching. Everything else on this page is a variation of that rule.
2. Example Workbook Setup
Imagine a workbook with two sheets:
| Sheet | Purpose |
|---|---|
Orders |
Contains product IDs entered in each order row |
Products |
Contains the master product list, names, categories, and prices |
On the Orders sheet:
| Order ID | Product ID | Product Name | Price |
|---|---|---|---|
| O-1001 | P-101 | ||
| O-1002 | P-205 | ||
| O-1003 | P-318 |
On the Products sheet:
| Product ID | Product Name | Category | Price |
|---|---|---|---|
| P-101 | Keyboard | Accessories | 45 |
| P-205 | Monitor | Displays | 220 |
| P-318 | Mouse | Accessories | 25 |
To return the product name into Orders!C2, use:
=VLOOKUP(B2,Products!$A$2:$D$100,2,FALSE)To return the price into Orders!D2, use:
=VLOOKUP(B2,Products!$A$2:$D$100,4,FALSE)After the first formula works, fill it down the order list.
3. Use Quotes When Sheet Names Have Spaces
If the sheet name contains spaces, punctuation, or special characters, wrap the sheet name in single quotes:
=VLOOKUP(A2,'Product List'!$A$2:$D$100,3,FALSE)This is one of the most common cross-sheet formula mistakes.
| Sheet name | Reference format |
|---|---|
Products |
Products!$A$2:$D$100 |
Product List |
'Product List'!$A$2:$D$100 |
2026 Prices |
'2026 Prices'!$A$2:$D$100 |
You do not have to type the sheet reference manually. Start the VLOOKUP formula, then click the other sheet tab and select the lookup range. Excel will insert the sheet reference for you.
4. Lock the Lookup Range Before Filling Down
When you copy a VLOOKUP formula down many rows, lock the lookup table with dollar signs:
=VLOOKUP(B2,Products!$A$2:$D$100,2,FALSE)If you use a relative range like Products!A2:D100, the reference can shift as
you copy the formula:
=VLOOKUP(B2,Products!A2:D100,2,FALSE)That can make later rows search the wrong part of the product table.
| Reference | What happens when copied down |
|---|---|
Products!A2:D100 |
Can shift to Products!A3:D101 |
Products!$A$2:$D$100 |
Stays fixed |
If the dollar signs feel confusing, review
Excel cell references, especially absolute
references and the F4 shortcut.
5. Make Sure the Table Array Starts with the Lookup Column
Cross-sheet VLOOKUP still follows the normal VLOOKUP rule: the first column of the table array must contain the value you are searching for.
This works if product IDs are in column A on the Products sheet:
=VLOOKUP(B2,Products!$A$2:$D$100,4,FALSE)This does not work if the Products sheet starts with another column, such as
Category, and product IDs are actually in column B:
| Category | Product ID | Product Name | Price |
|---|---|---|---|
| Accessories | P-101 | Keyboard | 45 |
| Displays | P-205 | Monitor | 220 |
=VLOOKUP(B2,Products!$A$2:$D$100,4,FALSE)That formula searches the Category column, not the Product ID column. In that case, start the range at column B:
=VLOOKUP(B2,Products!$B$2:$D$100,3,FALSE)If you need to search a column and return a value from a column to the left, VLOOKUP is the wrong tool. Use XLOOKUP or INDEX MATCH instead.
6. Fix #N/A in Cross-Sheet VLOOKUP
When VLOOKUP across sheets returns #N/A, the most likely causes are the same
as a normal VLOOKUP, but they are easier to miss because the source data is on
another tab.
| Problem | Fast check |
|---|---|
| Lookup value does not exist on the source sheet | Search the first column of the source table |
| Extra spaces or hidden characters | Compare =LEN(A2) with =LEN(TRIM(A2)) |
| Numbers stored as text | Test both sides with =ISNUMBER(A2) |
| Wrong table range | Confirm the source range starts with the lookup column |
| Formula copied down and range shifted | Use absolute references such as $A$2:$D$100 |
If missing values are expected, wrap the formula with IFNA after checking the
lookup logic:
=IFNA(VLOOKUP(B2,Products!$A$2:$D$100,2,FALSE),"Not found")For a deeper checklist, use How to Fix VLOOKUP #N/A in Excel.
Cross-Sheet Symptom Table
Cross-sheet formulas fail differently from same-sheet ones, because a wrong sheet reference is hard to see once the formula is collapsed in the formula bar. Find your symptom here.
| What you see | Likely cause | Fix |
|---|---|---|
#N/A on every row |
The selected range starts on a column that is not the key column, so VLOOKUP searches that column instead | Re-select the range from the key column onwards |
#N/A on some rows |
The source sheet has no matching key, or the two keys have different data types | Test one key with =COUNTIF(Products!$A$2:$A$100,B2) |
#REF! |
The source sheet was deleted, or the formula now points at a sheet the workbook does not contain | Rebuild the reference by clicking the sheet tab instead of typing the name |
#NAME? |
The formula uses a table name that is misspelled or no longer defined | Check the name in the Name Box, or re-select the source table |
| Right value in one row, wrong value below | The source range was not locked before the formula was filled down | Use $A$2:$D$100, not A2:D100 |
The COUNTIF test works across sheets with no extra setup, because the sheet
reference is simply part of the range. If it returns 0, the key is missing on the
source sheet. If it returns a value above 0 while VLOOKUP still shows #N/A, the
two keys disagree on data type or hold hidden spaces.
7. Use Excel Tables for Safer Cross-Sheet Lookups
Fixed ranges work, but Excel Tables are safer when the source list grows.
Convert the source range on the Products sheet to a table with Ctrl+T, then
give it a clear name such as ProductsTable.
Then your formula can use the table name:
=VLOOKUP(B2,ProductsTable,2,FALSE)This has two advantages:
| Benefit | Why it helps |
|---|---|
| New rows are included automatically | The table expands when products are added |
| The formula is easier to read | ProductsTable is clearer than Products!$A$2:$D$100 |
If you use many lookup formulas, named tables usually make the workbook easier to audit than long worksheet ranges.
8. VLOOKUP Across Sheets vs Across Workbooks
Looking up from another worksheet in the same workbook is usually reliable. Looking up from another workbook works too, but it is more fragile because the external file path can change.
Example external workbook reference:
=VLOOKUP(A2,'[Price List.xlsx]Products'!$A$2:$D$100,4,FALSE)Use cross-workbook VLOOKUP only when the source file is stable and shared with everyone who needs the formula. If the source workbook is moved, renamed, or not available, the formula can break or ask users to update links.
For most small business spreadsheets, it is simpler to keep the lookup table in the same workbook on a separate sheet.
9. When XLOOKUP Is Cleaner
If you use Microsoft 365 or Excel 2021 or later, XLOOKUP is usually cleaner for new workbooks:
=XLOOKUP(B2,Products!$A$2:$A$100,Products!$B$2:$B$100,"Not found")Compared with VLOOKUP, XLOOKUP:
| Need | VLOOKUP | XLOOKUP |
|---|---|---|
| Search another sheet | Yes | Yes |
| Exact match by default | No | Yes |
| Custom not-found message | Needs IFNA |
Built in |
| Look left | No | Yes |
| Avoid column counting | No | Yes |
Use VLOOKUP when you need compatibility with older Excel versions or inherited workbooks. Use XLOOKUP when you are building a new workbook in modern Excel.
For a broader decision guide, see VLOOKUP vs XLOOKUP vs INDEX MATCH.
10. Practice Example
Use this setup:
On the Sales sheet:
| Product ID | Product Name | Unit Price |
|---|---|---|
| P-205 | ||
| P-318 | ||
| P-999 |
On the Product List sheet:
| Product ID | Product Name | Unit Price |
|---|---|---|
| P-101 | Keyboard | 45 |
| P-205 | Monitor | 220 |
| P-318 | Mouse | 25 |
Tasks:
| Task | Goal |
|---|---|
| 1 | Return the product name from Product List |
| 2 | Return the unit price from Product List |
| 3 | Return Not found for missing product IDs |
Reference answers:
| Task | Formula |
|---|---|
| Return product name | =VLOOKUP(A2,'Product List'!$A$2:$C$4,2,FALSE) |
| Return unit price | =VLOOKUP(A2,'Product List'!$A$2:$C$4,3,FALSE) |
Return Not found |
=IFNA(VLOOKUP(A2,'Product List'!$A$2:$C$4,2,FALSE),"Not found") |
11. Summary
To use VLOOKUP across sheets, reference the lookup table with
SheetName!range, lock the source range before filling down, and use quotes
around sheet names with spaces. The lookup column must still be the first column
in the table array. If the formula returns #N/A, check the source sheet for
missing values, extra spaces, mismatched data types, and shifted ranges.
12. Related Guides
- Use the VLOOKUP troubleshooting guide when the formula returns the wrong result or a different error.
- Fix missing matches with How to Fix VLOOKUP #N/A in Excel.
- Combine sheet references with VLOOKUP with Multiple Criteria in Excel when one lookup value is not unique.
- Review Excel cell references before copying lookup formulas down a long list.
- Decide when a lookup should become a dynamic report with Excel FILTER vs VLOOKUP.
- Compare modern alternatives in Excel XLOOKUP tutorial.
- Choose the best lookup formula with VLOOKUP vs XLOOKUP vs INDEX MATCH.
Need help fixing a formula?
Share the formula, a small sample table, and the result you expected. This free beta helps us learn which Excel problems readers actually need solved.
Ask for formula help