VLOOKUP Between Two Sheets, Step by Step
By ExcelReady TeamUpdated 6 min read
The short answer
To VLOOKUP between two sheets, add the sheet name and an exclamation mark before the table range: =VLOOKUP(B2,Products!$A$2:$C$6,3,FALSE). The easiest way is to start typing the formula, click the other sheet's tab, select the table, and press Enter. If the sheet name has spaces, Excel wraps it in single quotes, like 'Price List'!$A$2:$C$6.
Key takeaways
- A reference to another sheet is the sheet name, an exclamation mark, then the range: Products!$A$2:$C$6.
- Sheet names with spaces or symbols need single quotes: 'Price List'!A2:C6.
- Click the other sheet's tab while typing and Excel writes the reference for you.
- Lock the table with $ signs before filling the formula down.
- Use FALSE for an exact match, and IFNA or IFERROR to handle missing codes.
On this page+
A VLOOKUP between two sheets works exactly like a normal VLOOKUP, except the table range starts with the other sheet's name and an exclamation mark, such as Products!$A$2:$C$6. You don't have to type that part yourself: click the other sheet's tab while writing the formula and Excel fills it in. This guide walks through it step by step, then covers sheet names with spaces, other workbooks and the errors that come up most.
The two sheets in this example#
The workbook has two sheets. The Orders sheet lists what customers bought, and the Products sheet holds the price list:
Products sheet:
| A | B | C | |
|---|---|---|---|
| 1 | Code | Product | Price |
| 2 | P-101 | Notebook | 3.50 |
| 3 | P-102 | Pen | 1.20 |
| 4 | P-103 | Stapler | 8.99 |
| 5 | P-104 | Folder | 2.40 |
| 6 | P-105 | Marker | 1.75 |
Orders sheet:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Code | Qty | Price |
| 2 | O-1 | P-103 | 2 | |
| 3 | O-2 | P-101 | 10 | |
| 4 | O-3 | P-105 | 4 |
The goal is to fill column D on the Orders sheet with the price of each code from the Products sheet. Keeping a price list on its own sheet is a good habit: one table, used by many formulas.
How to VLOOKUP between two sheets, step by step#
Start on the sheet where you want the result, not the sheet that holds the table.
- On the Orders sheet, click D2.
- Type
=VLOOKUP(B2,(the code to look up, then a comma). - Click the Products sheet tab at the bottom of the window. The formula bar now shows
=VLOOKUP(B2,Products!. - Select A2:C6 on the Products sheet.
- Press F4 to lock the range with
$signs (Mac: ⌘T). It becomesProducts!$A$2:$C$6. - Type
,3,FALSE)and press Enter.
Excel jumps back to the Orders sheet and shows the result:
=VLOOKUP(B2,Products!$A$2:$C$6,3,FALSE)D2 shows 8.99, the price of P-103. Don't click back to the Orders sheet before pressing Enter: that would change the formula. Just press Enter while you are still on the Products sheet.
You can also move between sheets with the keyboard while typing: Ctrl+Page Down and Ctrl+Page Up on Windows, or Option+Right arrow and Option+Left arrow on a Mac. The worksheets and cross-sheet references lesson practises this skill on its own.
What each part of the formula means#
The only new part is the sheet name. Everything else is a normal VLOOKUP:
| Part | Meaning |
|---|---|
B2 | The code to find, on the current sheet |
Products! | The sheet that holds the table, followed by ! |
$A$2:$C$6 | The table, locked so it doesn't move when you fill down |
3 | Return the 3rd column of the table (Price) |
FALSE | Exact match only |
The column number counts from the first column of the table, not from column A of the sheet. If you are unsure which number to use, see the VLOOKUP column index number. FALSE matters too: without it, VLOOKUP does an approximate match and can return a wrong price with no warning, as explained in VLOOKUP exact match.
Fill the formula down the column#
Select D2:D4 and fill down (Windows: Ctrl+D; Mac: ⌘D). Because the table is locked with $, every row still points at Products!$A$2:$C$6, while B2 changes to B3 and B4. The results are 8.99, 3.50 and 1.75.
If you forget the $ signs, the table slides down one row each time: D3 looks in Products!A3:C7, D4 in Products!A4:C8, and codes near the top of the list stop being found. This is one of the most common reasons for a VLOOKUP that works on the first row and fails further down.
To make room for new products, you can point at whole columns instead: Products!$A:$C. A neater option is to turn the price list into an Excel table (Ctrl+T on Windows, ⌘T on a Mac) so the range grows automatically.
Sheet names with spaces need quotes#
If a sheet name contains a space or certain symbols, Excel wraps it in single quotes:
=VLOOKUP(B2,'Price List'!$A$2:$C$6,3,FALSE)When you click the tab, Excel adds the quotes for you. If you type the reference by hand, add them yourself. Without them, Excel can't read the reference and the formula fails. Short names without spaces, like Products or Prices, are easier to work with.
If you rename a sheet later, Excel updates every formula that points at it. Deleting the sheet, however, turns those formulas into #REF!.
VLOOKUP from another workbook#
The same click-to-select method works across files. Open both workbooks, start the formula in one, switch to the other (on Windows, View > Switch Windows), select the table and press Enter. Excel adds the file name in square brackets:
=VLOOKUP(B2,[Prices.xlsx]Products!$A$2:$C$6,3,FALSE)When the source file is closed, Excel shows its full folder path in the formula. Links between files break easily when files are moved or renamed, so copy the price list into the same workbook when you can.
Why a VLOOKUP between sheets returns an error#
These are the usual causes:
| Error | Likely cause | Fix |
|---|---|---|
| #N/A | Code not in the first column of the table, or has extra spaces | Check the code, clean with TRIM |
| #N/A further down only | Table not locked with $ | Press F4 on the range |
| #REF! | Column number larger than the table, or sheet deleted | Fix the number or the reference |
| Wrong price | FALSE missing, so approximate match was used | Add FALSE as the 4th argument |
Numbers stored as text on one sheet but as numbers on the other also cause #N/A, because "1001" and 1001 don't match. To show a message for codes that really are missing, wrap the formula in IFNA: =IFNA(VLOOKUP(B2,Products!$A$2:$C$6,3,FALSE),"Not found"). The guide to VLOOKUP not working covers more causes, and VLOOKUP for multiple columns shows how to bring back the product name and price at once.
Practise it#
Cross-sheet lookups are standard in job tests, where the data often sits on one sheet and the questions on another. Practise VLOOKUP in a live spreadsheet until the four arguments are automatic, then practise references across worksheets. Each lesson checks your formula instantly. For a refresher on the basics, read VLOOKUP step by step or the VLOOKUP function guide.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
To keep this exercise on one sheet, the product list sits in D2:E6; on a real file it would be on its own sheet. In B2, use an exact-match VLOOKUP to return the price for the code in A2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Price | Price | ||||
| 2 | P-103 | |||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 | ||||||
| 9 |
Tip: Look up A2 in D2:E6, return column 2, and finish with FALSE for an exact match.
Practise this in a lesson
Frequently asked questions
How do I do a VLOOKUP between two sheets in Excel?
Start the formula on the sheet where you want the result, for example =VLOOKUP(B2, then click the other sheet's tab and select the table. Excel adds the sheet name, such as Products!A2:C6. Finish with the column number and FALSE, then press Enter.
What does the exclamation mark mean in a VLOOKUP?
It separates the sheet name from the cell range. Products!A2:C6 means cells A2 to C6 on the sheet called Products.
Why does my VLOOKUP from another sheet return #N/A?
The lookup value wasn't found in the first column of the table. Check for extra spaces, numbers stored as text, a missing FALSE, or a table that doesn't start at the lookup column.
How do I VLOOKUP from another workbook?
Open both files, start the formula, then switch to the other workbook and select the table. Excel adds the file name in square brackets, such as [Prices.xlsx]Products!$A$2:$C$6. Keep the source file in the same place, or the link can break.
What happens if I rename the sheet?
Excel updates the formula for you, so a VLOOKUP pointing at Products changes to the new name automatically. Deleting the sheet is different: the formula then shows #REF!.