How to Use XLOOKUP in Excel (2025) - Excel Tutorial Expert

Introduction

Excel has always been a powerful tool for data management, and with each new version, Microsoft continues to enhance its capabilities. One such powerful function that has replaced VLOOKUP and HLOOKUP is XLOOKUP in Excel. This function simplifies searching for values in Excel, making data retrieval faster and more flexible.

In this comprehensive guide, we will explore how to use XLOOKUP in Excel (2025) with step-by-step instructions, practical examples, and expert tips.

What is XLOOKUP?

XLOOKUP is an advanced lookup function that allows users to find values in a dataset with more efficiency compared to traditional lookup functions like VLOOKUP and HLOOKUP. It can search both vertically and horizontally, making it highly versatile.

Check How to Recover an Unsaved Excel Document in 20 Seconds

Why Use XLOOKUP Over VLOOKUP?

No Need for Column Index Numbers: Unlike VLOOKUP, where you need to specify the column index, XLOOKUP automatically finds the right value.
Works Left to Right & Right to Left: Unlike VLOOKUP, which only searches from left to right, XLOOKUP can search in any direction.
Handles Errors Gracefully: You can specify what should be returned if the lookup value is not found.
Faster and More Efficient: XLOOKUP is optimized for speed and flexibility.

Syntax of XLOOKUP

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

Arguments Explanation:

lookup_value: The value you want to search for.
lookup_array: The range where Excel should search for the lookup_value.
return_array: The range from which Excel should return the result.
[if_not_found] (optional): What should be returned if no match is found.
[match_mode] (optional): Controls how Excel matches values (exact match, approximate match, or wildcard match).
[search_mode] (optional): Defines the search direction (from first to last or last to first).

How to Use XLOOKUP in Excel – Step-by-Step Guide

Example 1: Basic XLOOKUP Usage

Imagine you have a dataset of employees with their Employee ID, Name, and Salary. You want to find an employee’s salary based on their Employee ID.

Dataset:

Employee ID	Name	Salary
101	Alice	$50,000
102	Bob	$55,000
103	Charlie	$60,000
104	David	$65,000

Formula to Get Salary for Employee ID 103:

=XLOOKUP(103, A2:A5, C2:C5)

Result: $60,000

Example 2: Using the [if_not_found] Argument

If the lookup value does not exist, XLOOKUP can return a custom message instead of an error.

=XLOOKUP(105, A2:A5, C2:C5, “Not Found”)

Result: “Not Found”

Example 3: Searching from Right to Left

Unlike VLOOKUP, XLOOKUP can search from right to left, eliminating the need to rearrange data.

=XLOOKUP(“Charlie”, B2:B5, A2:A5)

Result: 103 (Charlie’s Employee ID)

Example 4: Using Wildcards for Partial Matches

XLOOKUP allows wildcard searches when looking up text-based values. Use match_mode = 2 to enable wildcards.

=XLOOKUP(“*Bob*”, B2:B5, C2:C5, “Not Found”, 2)

Result: $55,000

Example 5: Searching from Bottom to Top (Reverse Order)

If you have duplicate values and need to return the last occurrence, use the search_mode argument.

=XLOOKUP(102, A2:A5, C2:C5, “Not Found”, 0, -1)

Result: $55,000

XLOOKUP vs. VLOOKUP vs. HLOOKUP – Key Differences

Feature	XLOOKUP	VLOOKUP	HLOOKUP
Search Direction	Any	Left to Right	Top to Bottom
Supports Wildcards	Yes	No	No
Works on Vertical & Horizontal Data	Yes	No	No
Handles Missing Values	Yes	No	No
Performance	Faster	Slower	Slower

Common Errors & How to Fix Them

Error	Cause	Solution
#N/A	Value not found	Use if_not_found argument
#VALUE!	Mismatched array sizes	Ensure lookup_array and return_array have the same size
#REF!	Reference error	Check cell references

Conclusion

XLOOKUP is one of the most powerful Excel functions introduced in recent years. It eliminates the limitations of VLOOKUP and HLOOKUP, allowing users to search data more efficiently and flexibly. Whether you’re looking up values vertically or horizontally, dealing with missing values, or searching in reverse order, XLOOKUP is the ultimate solution.

By mastering XLOOKUP in Excel 2025, you can save time, reduce errors, and improve productivity. Start using XLOOKUP today and make your Excel workflows seamless!

If you found this guide helpful, share it with others and leave a comment below with your thoughts!