Need to pull a price, name, or department from another tab? You can use VLOOKUP across two different sheets in Excel by adding the sheet name in front of the lookup range. The formula takes a minute to write and saves hours of copying and pasting. The steps below use a simple orders and products example that matches what most people deal with at work.
Key Takeaways
- Point the table_array argument at the other sheet using the format SheetName!Range, such as Products!$A$2:$C$100.
- Wrap sheet names that contain spaces in single quotes, such as ‘Price List’!$A$2:$C$100.
- Use FALSE as the last argument to get an exact match.
- Lock the lookup range with F4 so the formula can be copied down without the range shifting.
- The value being searched for must sit in the first column of the lookup range.
How to Use VLOOKUP Across Two Different Sheets in Excel
The example below uses two sheets in the same workbook. The sheet named Orders lists product IDs in column A, and the sheet named Products holds the master list. Products has IDs in column A, product names in column B, and prices in column C. The goal is to fill in the price on the Orders sheet automatically.
Step-by-Step Instructions
- Step 1: Open the workbook and check the layout of both sheets. On the Products sheet, the ID column must be the leftmost column of the range being searched, because VLOOKUP only looks to the right.
- Step 2: Go to the Orders sheet and click the cell where the first result should appear, for example C2.
- Step 3: Type =VLOOKUP( to start the formula. The basic structure is =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup).
- Step 4: Click cell A2 on the Orders sheet for the lookup value, then type a comma. This tells Excel which ID to search for.
- Step 5: Click the Products sheet tab, then select the range A2:C100 by dragging across it. Excel fills in the sheet reference automatically, so the formula now reads Products!A2:C100. Press F4 once to lock it as Products!$A$2:$C$100, then type a comma.
- Step 6: Type 3 as the column index number. Column C is the third column in the selected range, so Excel returns the price. Type a comma after it.
- Step 7: Type FALSE for an exact match, then close the bracket. The finished formula is =VLOOKUP(A2,Products!$A$2:$C$100,3,FALSE).
- Step 8: Press Enter. The price for that product ID appears in C2.
- Step 9: Double-click the small square at the bottom-right corner of C2 to copy the formula down the column. Every row now pulls its own price from the Products sheet.
Tip: The Formulas tab also offers a guided route. Go to Formulas, choose Lookup and Reference, then click VLOOKUP to open the Function Arguments box, where each argument has its own field.
VLOOKUP Across Two Sheets When the Sheet Name Has Spaces
Real workbooks rarely have tidy names like Products. A tab called Price List or Employee Data is far more common, and Excel needs single quotes around any sheet name that includes a space. Think of the quotes as a fence that keeps the name together as one piece.
Example With a Sheet Named Price List
- Step 1: Click the cell that should hold the result on the current sheet.
- Step 2: Type =VLOOKUP( and click the cell that holds the value to search for.
- Step 3: Type a comma, click the Price List tab, and select the lookup range. Excel adds the quotes on its own, producing ‘Price List’!$A$2:$C$100.
- Step 4: Finish with the column number and FALSE. The result looks like =VLOOKUP(A2,’Price List’!$A$2:$C$100,3,FALSE).
Tip: Selecting the range with the mouse is safer than typing it by hand, since Excel handles the quotes and exclamation mark automatically and typos are far less likely.
Example Returning a Name Instead of a Price
The same approach works for any column in the lookup range. To return the product name from the Products sheet instead of the price, change the column index number from 3 to 2: =VLOOKUP(A2,Products!$A$2:$C$100,2,FALSE). Count columns from the first column of the selected range, not from column A of the sheet.
Common Problems and Fixes
The Formula Returns #N/A
This means Excel could not find the lookup value in the first column of the range. Check for typos, extra spaces, or a value that simply does not exist on the other sheet. Numbers stored as text on one sheet and as true numbers on the other also cause this, so make sure both columns use the same data type. Wrapping the lookup value in TRIM, as in TRIM(A2), removes stray spaces.
The Formula Returns #REF!
This appears when the column index number is larger than the number of columns in the selected range. If the range is A2:C100, the highest valid column number is 3. Either lower the number or widen the range.
The Formula Returns #VALUE!
The column index number must be 1 or higher. A zero or negative number triggers this error, so correct the third argument.
Results Change or Break When the Formula Is Copied Down
This happens when the lookup range is not locked. Without dollar signs, the range moves down one row with each copied formula and eventually skips the matching rows. Click inside the range in the formula bar and press F4 to add the dollar signs.
The Formula Shows as Text Instead of a Result
If the cell displays the formula itself, the cell is probably formatted as Text. Change the format to General on the Home tab, click into the cell, and press Enter again.
Excel Returns the Wrong Match
Leaving out the last argument, or typing TRUE, makes VLOOKUP use an approximate match, which can return incorrect rows on unsorted data. Always type FALSE for IDs, names, and codes.
Frequently Asked Questions
Can VLOOKUP pull data from a different sheet in the same workbook?
Yes. Put the sheet name, an exclamation mark, and the range in the second argument, for example =VLOOKUP(A2,Products!$A$2:$C$100,3,FALSE).
How do I write the sheet name in a VLOOKUP formula?
Type the sheet name followed by an exclamation mark and the cell range, such as Products!$A$2:$C$100. If the name contains spaces, enclose it in single quotes, such as ‘Price List’!$A$2:$C$100.
Why does VLOOKUP return #N/A when the value clearly exists on the other sheet?
The most common causes are extra spaces, numbers stored as text, or a lookup value that is not in the first column of the selected range. Cleaning the data or using TRIM usually fixes it.
Should the last argument be TRUE or FALSE?
Use FALSE for an exact match, which suits IDs, names, and codes. TRUE returns an approximate match and requires the first column to be sorted in ascending order.
How do I stop the lookup range from changing when the formula is copied down?
Press F4 while the range is selected in the formula bar. This adds dollar signs and locks the range, giving a reference like Products!$A$2:$C$100.
Can VLOOKUP look to the left of the lookup column?
No. VLOOKUP only returns values from columns to the right of the first column in the range. The lookup column must be the leftmost column of the range, so rearrange the source data if needed.
How do I choose the correct column index number?
Count the columns inside the selected range, starting at 1 for the first column. In the range A2:C100, column A is 1, column B is 2, and column C is 3.
Do both sheets need to be in the same workbook?
No. VLOOKUP can also reference a sheet in another open workbook, but the steps above cover sheets within one workbook, which is the most reliable setup.
