Matching data between two sheets in the same workbook is one of the most common VLOOKUP use cases - one sheet holds the full data, another needs to pull specific columns from it based on a shared ID. The only real difference from a single-sheet formula is including the sheet name in table_array.
This page walks through that process step by step using an employee data sheet and a separate summary sheet.
Sheet "EmployeeData" contains the full table:
| Employee ID | Name | Department | Salary |
|---|---|---|---|
| E101 | Asha Rao | Marketing | 54000 |
| E102 | Vikram Shah | Finance | 61000 |
| E103 | Priya Nair | Sales | 48000 |
A second sheet, "Summary", contains just Employee IDs in column A, with Department needed in column B.
Click the cell on Summary where the result should appear, and begin typing =VLOOKUP( followed by the lookup value cell on that same sheet, e.g. A2.
While still typing the formula, click the EmployeeData tab and select the range there - Excel automatically inserts the sheet name into the reference:
Press F4 after selecting EmployeeData!A2:D4 to lock it with $ signs, so copying the formula down the Summary sheet doesn't shift the range away from the source data.
Turbo Excel VLookup can treat two sheets within the same workbook the same way it treats two separate files - load the workbook, pick EmployeeData and Summary as the two tables, choose the matching ID columns, and run the match across every row at once.
A surprising number of VLOOKUP problems turn out to be a data type mismatch rather than a logic error - a value stored as text in one column and as a number in the other will never match, even if they look identical on screen.
Before assuming the formula itself is wrong, select the lookup value's cell and check the small green triangle or the number alignment; numbers align right by default, and text aligns left, which is a fast way to spot the mismatch.
In a shared workbook, a bare VLOOKUP formula gives the next person no indication of what it's supposed to be matching or why. Adding a cell comment, or a short label in an adjacent column, explaining the source and purpose of the lookup saves significant time when the workbook is revisited months later or handed to a colleague.
This becomes even more important once a workbook has several lookups feeding into each other, since untangling a chain of undocumented formulas is far harder than reading a documented one.
How do I reference a different sheet in a VLOOKUP formula?
Include the sheet name followed by an exclamation mark before the range, such as EmployeeData!A2:D4.
Does clicking into another sheet while typing insert the reference automatically?
Yes, Excel writes the correct SheetName! prefix automatically when you click into a different sheet mid-formula.
Why lock the range with $ signs for a cross-sheet formula?
So the referenced range stays fixed on the other sheet when the formula is copied down the current sheet.
What happens if the other sheet is renamed?
The formula's sheet reference breaks and needs to be manually corrected to match the new sheet name.
Can VLOOKUP reference a sheet that comes after the current one in tab order?
Yes, sheet order has no effect on whether a VLOOKUP reference works.
Is there a way to match two sheets without writing this formula?
Yes, Turbo Excel VLookup can match two sheets within the same workbook using the same dropdown-based process as two separate files.
Should I test a new formula on one row before applying it everywhere?
Yes, verifying a single result against the source data first catches setup mistakes before they're copied across an entire column.
Is it a good idea to edit source data while formulas are actively referencing it?
It's safer to keep a stable source sheet separate from a working sheet, since editing referenced data mid-use can produce confusing intermediate results.
Why would a text-formatted number fail to match a real number?
Excel treats text and numeric values as different data types, so "101" stored as text won't match 101 stored as a number even though they display the same.
Does locking a range with F4 change how the formula calculates?
No, it only fixes the range so it doesn't shift when the formula is copied - the calculation itself works the same either way.
Ready to stop fighting #N/A errors and match your spreadsheets visually?