XLOOKUP Across Two Sheets in Excel: A Step-by-Step Guide with Example
We know Excel’s XLOOKUP function is a modern and powerful replacement for VLOOKUP and HLOOKUP. One of its best features is the ability to search for values across two different sheets in a workbook — a task that earlier required clunky combinations of functions or helper columns.
In this session, you’ll learn how to use XLOOKUP between two Excel sheets with a real-life example. This guide will help you not only understand the function but also apply it easily in your projects. After reading this article you can easily apply in your own project .
🔍 What is XLOOKUP?
XLOOKUP is a lookup function introduced in Excel 365 and Excel 2019+. Mean to say that if you are using older version of below 2019 this function will not show and work. It can:
- Search both vertically and horizontally
- Return values to the left or right (unlike VLOOKUP)
- Eliminate the need for column index numbers
- Provide default messages if no result is found
📝 Scenario Example: Lookup Between Two Sheets
For example if we want to working in a school and we have two Excel sheets:
Sheet 1: StudentMarks sheet have STUDENT ID , MARKS Columns
Sheet 2: StudentInfo Sheet have STUDENT ID, NAME, GRADE Columns
🎯 Goal: Get “Marks” from StudentMarks into StudentInfo using Student ID as the key.
💡 How to Use xlookup function Between Sheets
Formula Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
✅ Step 1: Go to StudentInfo Sheet
Click on the cell in column D2 where you want to pull the marks.
✅ Step 2: Enter the Formula in StudentInfo sheet
=XLOOKUP(A2, StudentMarks!A:A, StudentMarks!B:B, “Not Found”)
✅ Step 3: Drag the Formula Down
Drag the fill handle to apply the formula to other rows.
✅ Result:
🛠 Why This Works
- A2: Student ID in the current sheet (StudentInfo)
- StudentMarks!A:A: Column in the other sheet where Excel should search
- StudentMarks!B:B: Column from where data is fetched (Marks)
- “Not Found”: This is for error msg if data is not fetch properly it will show msg not found . it will avoid #N/A error in cell
✅ Use Cases for XLOOKUP Across Sheets
| Use Case | Example |
| HR Employee Database | Pull employee salary from a payroll sheet |
| Inventory Management | Get product stock level from warehouse sheet |
| Academic Records | Match subject marks with student ID |
| Finance Reports | Fetch payment details from a master sheet |
🧠 Pro Tips for Using Between Sheets
- Use Named Ranges to make formulas cleaner.
- Avoid entire column references in very large sheets for better performance.
- Use IFERROR() to wrap this function for additional error handling if needed.
- You can nest XLOOKUP inside other functions like IF, TEXT, or CONCAT.
🔄 Difference Between VLOOKUP and XLOOKUP (Recap)
| Feature | VLOOKUP | XLOOKUP |
| Lookup Direction | Only left to right | Any direction |
| Column Index | Required | Not required |
| Error Handling | Limited | Better with custom message |
| Performance | Slower on large data | Faster |
📝 Final Thoughts
Using this simplifies tasks that once required complex formulas. Whether you’re managing inventory, school records, or employee data — this tool will save you time and improve accuracy.
💬 Do you still have a doubt? Leave a comment below and we’ll assist you.
📩 Want more Excel tips? Subscribe to our newsletter or check our related posts:
❓ Frequently Asked Questions:
Yes, you can use XLOOKUP across different Excel files (workbooks), but the other file must be open while applying the formula.
Yes. XLOOKUP is faster, more flexible, and does not break if columns are inserted or deleted.
You can customize the result with a message like “Not Found” instead of seeing an error.