How to use VLOOKUP between two sheets in Excel
Look up a value on one sheet and return a matching field from a table on another sheet, with the table range locked so the formula can be filled down.
Lookups that cross sheets are the standard way to join two tables: orders on one sheet, a price list on another, and a formula that fetches the price for each order.
Building the formula by clicking, instead of typing the range, avoids most typing errors. The two details that decide whether it survives being filled down are the locked table range and the exact-match argument.
Steps in Excel for Windows
- Start the formula on the sheet that needs the answer. Click the cell next to the first row, for example C2 on the Orders sheet, and type
=VLOOKUP(. - Click the lookup value. Click the key in that row, such as the SKU in A2, then type a comma.
- Select the table on the other sheet. Click the other sheet's tab and drag across the table, from the key column through the column you want back. Excel writes the sheet name for you, such as
Prices!A2:B3. Type a comma. - Lock the table range. With the table reference still selected in the formula bar, press F4 once. It turns into
Prices!$A$2:$B$3, so it does not move when the formula is copied down. - Give the column number and FALSE. Type the position of the answer column counted from the first column of the table, here 2, then a comma and FALSE for exact match. Close the bracket and press Enter.
- Fill down. Double-click the fill handle. Each row now looks up its own SKU in the same locked table.
Worked example
1. Price from the Prices sheet
| A | B | C | |
|---|---|---|---|
| 1 | SKU | Qty | Price |
| 2 | B-100 | 3 | 2.25 |
| 3 | A-200 | 5 |
| A | B | |
|---|---|---|
| 1 | SKU | Price |
| 2 | A-200 | 4.5 |
| 3 | B-100 | 2.25 |
On Sheet1, A2 holds the SKU B-100. VLOOKUP searches the first column of Prices!$A$2:$B$3, finds B-100 in the second row, and returns column 2, the price.
2. A sheet name with a space needs quotes
| A | B | C | |
|---|---|---|---|
| 1 | SKU | Qty | Price |
| 2 | B-100 | 3 | 2.25 |
| 3 | A-200 | 5 |
| A | B | |
|---|---|---|
| 1 | SKU | Price |
| 2 | A-200 | 4.5 |
| 3 | B-100 | 2.25 |
When the sheet is called Price List, wrap the name in single quotation marks. Excel adds them automatically if you click to select the range.
3. A friendly message when the SKU is missing
| A | B | C | |
|---|---|---|---|
| 1 | SKU | Qty | Price |
| 2 | C-300 | 3 | Not listed |
| A | B | |
|---|---|---|
| 1 | SKU | Price |
| 2 | A-200 | 4.5 |
| 3 | B-100 | 2.25 |
The SKU in A2 does not appear on the Prices sheet, so VLOOKUP would return #N/A. IFNA catches that one error and shows the text instead.
Common mistakes
- Not locking the table range. Without dollar signs the range slides down as you fill, and lookups in later rows start missing items. Press F4 right after selecting the table.
- Counting the column number from the sheet. The number counts from the first column of the table you selected, not from column A. A table in D:F with the answer in F is column 3.
- Leaving off FALSE. Omitting the last argument means approximate match, which needs sorted data and can return a wrong row silently. Type FALSE for IDs and names.
- Key in the wrong column. VLOOKUP searches only the first column of the table. If the key sits to the right of the answer, use INDEX and MATCH or XLOOKUP.
- Keys that look the same but are not. A trailing space or a number stored as text makes a match fail with #N/A. Check the two keys with LEN, or clean them with TRIM.
On Excel job tests
The prompt gives two sheets and reads "use VLOOKUP to bring the unit price from the Prices sheet into the Orders sheet". Check four things before you fill down: the lookup value is in the current row, the table range is locked, the column number matches the table, and the last argument is FALSE.
Some tasks add "show Not found if the item is missing", which calls for IFNA or IFERROR around the lookup. If the prompt forbids VLOOKUP or puts the key on the right, use INDEX and MATCH.
Related guides
Questions
- How do I refer to another sheet in a formula?
- Type the sheet name, an exclamation mark and the range, such as
Prices!A2:B3. Sheet names with spaces need single quotes, as in'Price List'!A2:B3. - Why does my VLOOKUP return the wrong row?
- The last argument was left blank or set to TRUE, which turns on approximate matching. Use FALSE for an exact match.
- Can VLOOKUP look to the left?
- No. It searches the first column of the table. Use INDEX and MATCH, or XLOOKUP, to return a column to the left of the key.
- What does F4 do in the formula bar?
- It cycles the selected reference through absolute, mixed and relative forms. One press gives
$A$2:$B$3.
Practice this
Practice the VLOOKUP function itself from the function guide, which links to the lessons that cover it. The closest practice is the free 5-item sampler, which uses the same kind of tasks in a working grid.
Try a 5-item Excel test sample