Home › Excel functions › VLOOKUP › From another sheet
VLOOKUP from Another Sheet in Excel
Your lookup table doesn't have to be on the same sheet as your formula. With VLOOKUP you can pull a value from a list on another tab, or even from another workbook. The only difference is that you put the sheet name in front of the range.
VLOOKUP · In this guide
The formula for another sheet
A normal VLOOKUP points to a range like A2:C7. To look in another sheet, you add the sheet name and an exclamation mark in front of that range:
=VLOOKUP(B2, Customers!A2:C7, 2, FALSE)This looks up the value in B2 of the current sheet, searches the first column of A2:C7 on the sheet called Customers, and returns the value from the second column. Everything else works exactly like a normal VLOOKUP.
| Part | What it does |
|---|---|
| B2 | The lookup value, on the sheet where you write the formula. |
| Customers! | The sheet to search in. The exclamation mark separates the sheet name from the cells. |
| A2:C7 | The table on that sheet. The value you look for must be in its first column. |
| 2, FALSE | Return column 2 of that table, and only accept an exact match. |
Example: customer names on an invoice list
Say you have a sheet Invoices with a customer ID per invoice, and a separate sheet Customers with every customer's name and city. You want the name next to each invoice.
The Customers sheet:
| A | B | C | |
|---|---|---|---|
| 1 | ID | Name | City |
| 2 | C-101 | Bright Ideas Ltd | Leeds |
| 3 | C-102 | Northwind | York |
| 4 | C-103 | Blue Harbor | Bristol |
| 5 | C-104 | Green Valley Co | Leeds |
| 6 | C-105 | Oak & Pine | Bath |
| 7 | C-106 | Summit Supplies | Hull |
On the Invoices sheet, the formula in C2 is:
=VLOOKUP(B2, Customers!$A$2:$C$7, 2, FALSE)For invoice INV-2051 with customer C-104, it returns Green Valley Co. The $ signs lock the range, so you can copy the formula down to every invoice and it keeps looking in the same customer list.
You don't have to type the sheet name. Start the formula, type the comma after the lookup value, then click the Customers tab and select the cells. Excel writes the sheet name and the exclamation mark for you. Type the next comma and Excel stays on the other sheet until you press Enter.
Practice: VLOOKUP from another sheet
Build the formula below step by step. When the tip asks for the customer list, click the Customers tab under the spreadsheet and drag across the cells, just like in Excel.
Exercise: in cell C2 of the Invoices sheet, write =VLOOKUP(B2,Customers!A2:C7,2,FALSE) to show the customer name. Turn on JavaScript to try it interactively.
Sheet names with spaces or special characters
If the sheet name contains a space, a dash or another special character, Excel puts it between single quotes:
=VLOOKUP(B2, 'Customer list'!A2:C7, 2, FALSE)Forget the quotes and Excel shows an error message as soon as you press Enter. The easiest way to avoid this is to click the sheet instead of typing the name: Excel adds the quotes when they're needed.
VLOOKUP from another workbook
The same idea works across files. The file name goes between square brackets, in front of the sheet name:
=VLOOKUP(B2, [Customers.xlsx]Sheet1!$A$2:$C$7, 2, FALSE)While both files are open, this short form is enough. When you close the source file, Excel changes the reference into the full path, such as 'C:\Sales\[Customers.xlsx]Sheet1'!$A$2:$C$7, and keeps showing the last values it found.
Links to other files break easily. If someone renames or moves the source file, the formula can no longer update. For a list you use often, copying it into a sheet of the same workbook is usually more reliable.
Common errors with another sheet
| What you see | Likely cause | Fix |
|---|---|---|
| #N/A | The ID doesn't exist on the other sheet, or it differs by a space or a number stored as text. | Check the value on both sheets; see fixing #N/A in VLOOKUP. |
| #REF! | The column number is larger than the number of columns in the range, or the sheet was deleted. | Count the columns in the range on the other sheet. |
| A dialog box asks for a file | The sheet name is misspelled, so Excel thinks it's a file. | Click the sheet instead of typing its name. |
| Wrong results lower down | The range moved when you copied the formula. | Lock it: Customers!$A$2:$C$7 instead of Customers!A2:C7. |
More VLOOKUP guides
Frequently asked questions
How do I reference another sheet in VLOOKUP?
Put the sheet name and an exclamation mark in front of the range, like Customers!A2:C7. Or, while you write the formula, click the other sheet and select the cells: Excel adds the sheet name for you.
Why does VLOOKUP from another sheet return #N/A?
The lookup value isn't found in the first column of the range on the other sheet. Often the values look the same but differ: a trailing space, or an ID stored as text on one sheet and as a number on the other.
Can VLOOKUP search several sheets at once?
Not by itself. You can nest lookups with IFERROR, so that Excel tries the second sheet when the first one gives #N/A, but it's usually easier to combine the lists into one table first.
Does VLOOKUP to another workbook work when that file is closed?
Yes, Excel keeps the last values it found. It only updates them when you open the source file again or choose to update the links.