XLOOKUP Function in Excel – Complete Guide with Examples (Beginner to Advanced)

 The XLOOKUP function in Excel is one of the most powerful and useful formulas introduced by Microsoft in recent years. It is designed to replace older lookup functions like VLOOKUP, HLOOKUP, and even INDEX + MATCH in many situations.

In real office work, Excel users often need to find values such as employee names, salaries, product prices, marks, or sales data from large tables. Earlier, this was done using VLOOKUP, which had many limitations. To solve these problems, Microsoft introduced XLOOKUP.

In this blog post, you will learn:

• What XLOOKUP is
• Why it is better than VLOOKUP

• XLOOKUP syntax

• Multiple easy and practical examples
• Real-life use cases

This guide is perfect for beginners, students, and working professionals.

What Is XLOOKUP in Excel?

XLOOKUP is a lookup and reference function that searches for a value in one range and returns a corresponding value from another range.

Simple Definition:

XLOOKUP finds a value in a table and returns the related information from the same row or column.

It works both vertically and horizontally, making it more flexible than older lookup functions.

Why XLOOKUP Is Better Than VLOOKUP

Before XLOOKUP, most Excel users relied on VLOOKUP. However, VLOOKUP has several drawbacks.

Problems with VLOOKUP:

• Can only search from left to right
• Breaks when columns are inserted or deleted
• Requires column index number
• Returns #N/A error if value not found

Cannot return multiple values

Advantages of XLOOKUP:

• Works in any direction
• No column number required
• Default exact match
• Built-in error handling
• Can return multiple values
• Cleaner and easier formulas

Because of these benefits, XLOOKUP is now the recommended lookup function in Excel.    

XLOOKUP Function Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Explanation of XLOOKUP Arguments

1. lookup_value (Required)

The value you want to find.
Example: Employee ID, Product Name, Roll Number.

2. lookup_array (Required)

The range where Excel will search for the lookup value.

3. return_array (Required)

The range from which Excel will return the result.

4. if_not_found (Optional)

Value to return if the lookup value is not found.
Example: "Not Found"

5. match_mode (Optional)

• 0 → Exact match (default)
• -1 → Exact or next smaller
• 1 → Exact or next larger
• 2 → Wildcard match

6. search_mode (Optional)

• 1 → Search first to last (default)
• -1 → Search last to first
• 2 → Binary search (ascending)

-2 → Binary search (descending)

Basic Example of XLOOKUP

Data Table:

Formula:

=XLOOKUP(102, A2:A4, B2:B4)

Result:

Neha

XLOOKUP vs VLOOKUP (Key Differences)

Example 1: Find Salary Using XLOOKUP

=XLOOKUP(103, A2:A4, D2:D4)

Result: 40000

No column index is required, making the formula simple and safe.

---

Example 2: XLOOKUP with IF NOT FOUND

=XLOOKUP(105, A2:A4, B2:B4, "Employee Not Found")

Instead of showing #N/A, Excel displays a meaningful message.

---

Example 3: Right-to-Left Lookup Using XLOOKUP

With XLOOKUP, you can easily search from right to left.

=XLOOKUP("IT", C2:C4, B2:B4)

This returns the employee name working in the IT department.

---

Example 4: Horizontal Lookup (HLOOKUP Replacement)

Data:

=XLOOKUP("Feb", B1:D1, B2:D2)

Result: 7000

XLOOKUP completely replaces HLOOKUP.

---

Example 5: Wildcard Lookup Using XLOOKUP

=XLOOKUP("Ra*", B2:B4, D2:D4, "Not Found", 2)

This finds values that start with “Ra”.

---

Example 6: Find Last Match Using XLOOKUP

Useful when data contains duplicates.

=XLOOKUP(101, A2:A10, B2:B10, , , -1)

This returns the last matching record.

---

Example 7: Return Multiple Values (Spill Feature)

=XLOOKUP(102, A2:A4, B2:D4)

This returns Name, Department, and Salary together.

Works in Excel 365 and Excel 2021.

---

XLOOKUP with Dates Example

=XLOOKUP(DATE(2024,1,1), A2:A10, B2:B10, , -1)

Used in reports where approximate date matching is required.

Common Errors in XLOOKUP

1. Range size mismatch

Lookup array and return array must be the same size.

2. XLOOKUP not available

XLOOKUP works only in:

• Excel 365
• Excel 2021 and later

Not available in Excel 2016 or older versions.

---

When Should You Use XLOOKUP?

Use XLOOKUP when:

• You want to replace VLOOKUP
• You need flexible lookup direction
• You want clean error handling
• You work with large datasets
• You build dashboards or automation

---

Real-Life Uses of XLOOKUP

• Employee salary lookup
• Student marksheets
• Product price lists
• Sales and MIS reports
• Inventory management
• Excel automation projects

---

Important Tips for XLOOKUP

• Lock ranges using $ for dashboards
• Use default exact match when possible
• Add meaningful IF NOT FOUND messages
• Combine XLOOKUP with IF, SUM, FILTER

---

Conclusion

The XLOOKUP function in Excel is a modern, flexible, and powerful lookup solution. It removes the limitations of older functions and makes Excel formulas cleaner and easier to manage.