To use the vlookup formula in excel between two sheets, add the sheet name and an exclamation mark before the range: =VLOOKUP(A2,Sheet2!$A:$C,2,0). Excel looks up the value in A2 against column A on Sheet2 and returns the value from column B. That single syntax change pulls data across sheets instantly.
- Key Takeaway 1: Cross-sheet references use the format SheetName!Range, so
Sheet2!A:Cis a valid lookup range. - Key Takeaway 2: VLOOKUP can pull from another workbook using
[WorkbookName.xlsx]SheetName!Range, but the path changes when the source file is closed. - Key Takeaway 3: To get a value from another sheet with a condition, combine VLOOKUP with IF, or use FILTER for more flexible criteria.
- Key Takeaway 4: XLOOKUP handles cross-sheet and cross-workbook lookups with cleaner syntax and no column-index guessing.
- Key Takeaway 5: INDIRECT can build dynamic sheet references, but it breaks when the source workbook is closed, so use it carefully.
Sheet References in Lookups
Every cell in Excel has an address: a column letter and a row number. When you cross a sheet boundary, Excel simply prepends the sheet name. The full address looks like Sheet2!B2, and a range looks like Sheet2!B:D. If the sheet name contains spaces or special characters, wrap it in single quotes: 'Sales Data'!B:D.
This reference mechanic is the same whether you use VLOOKUP, INDEX-MATCH, or XLOOKUP. Understanding it once means you can apply it everywhere. Think of it as the postal address system for your spreadsheet: the sheet name is the city, the column and row are the street and house number.
When Sheet Names Cause Errors
A common mistake is renaming a sheet after you have already written your formula. Excel usually updates the reference automatically, but if you copy a formula from one workbook to another, the sheet name in the formula stays as it was. You end up with a #REF! error that can take a while to trace.
In practice, this happens constantly in shared finance or HR workbooks at Indian companies where someone renames “Sheet1” to “FY2025” mid-project. Always check your formula bar after a sheet rename, especially if you are handing the file to someone else. A real-world example: an HR analyst at a mid-sized Bengaluru firm tracking employee CTC across departments will often maintain a master sheet and pull data into monthly summary sheets using the vlookup formula in excel between two sheets — a sheet rename mid-cycle breaks every formula instantly.
VLOOKUP Across Two Sheets
Say you have a list of employee IDs in column A on Sheet1, and the full employee details (name, department, salary) live in columns A through C on Sheet2. To pull the department name into Sheet1, your formula in B2 would be:
=VLOOKUP(A2,Sheet2!$A:$C,2,0)
The 0 at the end forces an exact match, which is almost always what you want when working with IDs or codes. The 2 means return the second column from the lookup range, which is the department column. This is the core vlookup formula in excel between two sheets that most analysts use daily.
Step-by-Step: Writing the Formula
- Click the cell where you want the result to appear (for example, B2 on Sheet1).
- Type
=VLOOKUP(and click the cell that holds your lookup value (A2). - Type a comma, then click the Sheet2 tab, select your full data range (A:C), and type another comma.
- Enter the column index number (2 for the second column), add a comma, then type 0 for exact match.
- Close the bracket and press Enter. Excel writes the full reference for you.
Letting Excel write the reference by clicking into Sheet2 is the safest approach. You avoid typos in the sheet name and get the syntax right every time. This is one of those small habits that saves hours of debugging later.
Locking the Range for Multiple Rows
When you drag the formula down to B3, B4 and so on, the lookup value (A2) should shift but the table range on Sheet2 should not. Lock it with dollar signs: Sheet2!$A:$C. Without the lock, Excel shifts the range as you copy down and you get wrong results or errors.
This is one of the most common beginner mistakes covered in data analyst interview questions that involve Excel. Interviewers often ask candidates to spot the bug in a formula, and a missing dollar sign is a favourite trap.
VLOOKUP from Another Workbook
The vlookup formula in excel between two sheets extends naturally to two separate workbook files. When both files are open, Excel writes the reference like this:
=VLOOKUP(A2,[EmployeeData.xlsx]Sheet1!$A:$C,2,0)
The workbook name sits inside square brackets, followed immediately by the sheet name and then the range. Excel auto-generates this syntax when you click into the other workbook while typing your formula, which is the recommended way to do it.
What Happens When You Close the Source Workbook
This is where things get tricky. When the source workbook is closed, Excel converts the reference to an absolute file path:
=VLOOKUP(A2,'C:\Users\Priya\Documents\[EmployeeData.xlsx]Sheet1'!$A:$C,2,0)
The formula still works as long as the file has not moved. But if someone renames the folder, shifts the file to a shared drive, or opens the workbook on a different machine with a different directory structure, the formula breaks with a #REF! or #VALUE! error. According to Microsoft’s official Excel documentation, external references to closed workbooks require the full path to be accurate and accessible at all times.
Practical Tips for Cross-Workbook Lookups
- Keep source and destination workbooks in the same shared folder so the relative path stays consistent.
- If your team works across different machines, use a network path or SharePoint URL rather than a local drive path.
- Consider pasting values only (Ctrl+Shift+V, then Values) if the source data does not change often. This removes the dependency entirely.
- For dynamic, always-live connections, Power Query is a more reliable alternative than VLOOKUP across closed workbooks.
Excel skills like these are increasingly valued in analytics roles. According to the NASSCOM Future of Work Report 2024, data and analytics roles in India are projected to grow by over 30% through 2027, with Excel and SQL cited as foundational skills even for roles that eventually require Python. Highest paying tech skills in 2026 consistently include data wrangling, and Excel proficiency is the baseline expectation before you move into Python or SQL.
| Scenario | Syntax Example | Works When Closed? | Common Risk |
|---|---|---|---|
| Same workbook, different sheet | =VLOOKUP(A2,Sheet2!$A:$C,2,0) |
Yes (no external dependency) | Sheet rename breaks reference |
| Another open workbook | =VLOOKUP(A2,[File.xlsx]Sheet1!$A:$C,2,0) |
Yes, if path is correct | File moved or renamed |
| Another closed workbook | =VLOOKUP(A2,'C:\Folder\[File.xlsx]Sheet1'!$A:$C,2,0) |
Yes, with full path | Path changes across machines |
| Dynamic sheet name (INDIRECT) | =VLOOKUP(A2,INDIRECT("'"&B1&"'!A:C"),2,0) |
No (INDIRECT is volatile) | Breaks immediately when source is closed |
Getting Values from Another Sheet with Conditions
Standard VLOOKUP returns the first match it finds. If you need to apply a condition — say, returning a salary only if the employee is in the “Engineering” department — you need to combine lookups with logic functions. This is what “excel get value from another sheet with condition” means in practice.
Using IF with VLOOKUP
The simplest conditional approach wraps VLOOKUP inside an IF statement:
=IF(VLOOKUP(A2,Sheet2!$A:$C,2,0)="Engineering",VLOOKUP(A2,Sheet2!$A:$C,3,0),"")
This first checks whether the department (column 2) is “Engineering”. If yes, it returns the salary (column 3). If no, it returns blank. It runs VLOOKUP twice, which is slightly inefficient but perfectly readable for most datasets.
Using FILTER for Multi-Condition Lookups
If you are on Microsoft 365 or Excel 2021, the FILTER function is far cleaner for conditional extraction from another sheet:
=FILTER(Sheet2!$C:$C,(Sheet2!$A:$A=A2)*(Sheet2!$B:$B="Engineering"))
This returns all salary values from Sheet2 where the ID matches A2 AND the department is “Engineering”. The asterisk acts as an AND operator between conditions. FILTER spills results into multiple cells automatically, so you do not need to wrap it in anything.
Learning to combine lookup logic with conditional functions is exactly the kind of skill that separates a capable Excel user from someone who can genuinely contribute to data analytics projects. It is also the mental model you carry into Python when you start working with Pandas and NumPy explained for the same filtering tasks at scale.
Is XLOOKUP Better for Cross-Sheet Work?
For most use cases, yes. XLOOKUP does not need a column index number, so you are not counting columns and getting it wrong. It handles left-side lookups natively, returns a range instead of a single value, and has cleaner error handling built in. The syntax across sheets is identical to the vlookup formula in excel between two sheets: just replace the range with Sheet2!$A:$A for the lookup array and Sheet2!$C:$C for the return array.
The reason VLOOKUP still matters is compatibility. According to Statista’s 2024 enterprise software market share data, Excel remains the dominant spreadsheet tool in corporate environments globally, and many organisations in India still run Excel 2016 or 2019 where XLOOKUP is not available. Knowing the vlookup formula in excel between two sheets remains a practical, employable skill in that context.
It is also worth noting that VLOOKUP only looks to the right — it cannot return a value from a column to the left of the lookup column. XLOOKUP and INDEX-MATCH both solve this. If you are writing a vlookup formula in excel between two sheets and getting unexpected results, check whether your return column is to the right of your lookup column.
If you are thinking about where Excel fits in a broader analytics career, it is worth reading about the difference between data analyst vs data scientist roles, since analysts tend to live in Excel and SQL while scientists move quickly into Python and R. For anyone looking to move from Excel analytics into engineering, our guide on how to become a data engineer covers exactly that transition, including the tools and certifications that matter most.
Frequently Asked Questions
How do I VLOOKUP from another sheet?
Add the sheet name followed by an exclamation mark before your range. The formula looks like =VLOOKUP(A2,Sheet2!$A:$C,2,0). If the sheet name has spaces, wrap it in single quotes: 'Sales Data'!$A:$C. Excel writes this reference automatically if you click into the other sheet while building your formula.
Can VLOOKUP pull from another workbook?
Yes. When both workbooks are open, the reference includes the workbook name in square brackets: [EmployeeData.xlsx]Sheet1!$A:$C. When the source workbook is closed, Excel expands this to the full file path. The formula continues to work as long as the file stays in the same location.
Why does VLOOKUP break when the source workbook is closed?
Excel stores the full file path when a source workbook is closed. If the file is moved, renamed, or opened on a machine with a different directory structure, the path no longer points to anything real and the formula returns an error. Keeping source files in a stable shared location, or using Power Query for live connections, avoids this problem.
How do I get a value from another sheet with a condition?
Wrap VLOOKUP inside an IF statement to check a condition before returning a value, like =IF(VLOOKUP(A2,Sheet2!$A:$C,2,0)="Engineering",VLOOKUP(A2,Sheet2!$A:$C,3,0),""). On Microsoft 365 or Excel 2021, FILTER is cleaner and handles multiple conditions in a single formula without running the lookup twice.
Is XLOOKUP better than VLOOKUP across sheets?
For most tasks, yes. XLOOKUP does not require a column index number, supports left-side lookups natively, and has built-in error handling. The cross-sheet syntax is the same as VLOOKUP. The only reason to stick with VLOOKUP is compatibility with Excel versions older than 2021, which are still common in many Indian organisations running Excel 2016 or 2019.
What does the exclamation mark mean in an Excel formula?
The exclamation mark separates the sheet name from the cell or range reference. In Sheet2!$A:$C, everything before the exclamation mark is the sheet name and everything after is the range. It is Excel’s way of saying “on this sheet, look at this range.”
The vlookup formula in excel between two sheets is one syntax change away from what you already know: add the sheet name, an exclamation mark, and you are done. Cross-workbook lookups follow the same logic with a workbook name in square brackets, and conditional lookups layer IF or FILTER on top. Here are three things you can do this week to solidify this skill:
- Open any workbook you use at work or college, create a second sheet with reference data, and write a VLOOKUP that pulls from it. Do it by clicking into the sheet rather than typing the reference manually.
- Practice one FILTER formula with two conditions pulling from a separate sheet. Compare the result to the IF-VLOOKUP version and notice which is easier to read and maintain.
- Explore common data analyst interview questions that test Excel skills, and check which ones you can now answer confidently.
If you are ready to move beyond Excel into a full analytics or cybersecurity career, explore the EC-Council Certified SOC Analyst course at 3.0 University, or browse the full 3.0 University online certification courses catalogue covering Cybersecurity, AI, Ethical Hacking, Blockchain and Web3.
Last updated: October 2026. Reviewed by the 3University editorial team.