The XLOOKUP function in Excel is one of the most powerful lookup functions available. It helps you search for a value in a table and return matching information from another column or row. XLOOKUP is easier to use and more flexible than older lookup functions like VLOOKUP and HLOOKUP because it can search in any direction and includes built-in error handling.
Businesses commonly use XLOOKUP for employee databases, inventory tracking, customer records, product pricing, budgeting, and financial reports.
In This Guide, You’ll Learn
- What the XLOOKUP function does
- The syntax and arguments of XLOOKUP
- How to retrieve matching data from tables
- Practical XLOOKUP examples for beginners
- Common mistakes to avoid
- The differences between XLOOKUP and VLOOKUP
What is the XLOOKUP Function?
The XLOOKUP function searches for a value in one range and returns a matching value from another range, making it ideal for finding information in spreadsheets.
XLOOKUP Function Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Argument Definitions
- lookup_value – The value you want to find.
- lookup_array – The range containing the value you want to search for.
- return_array – The range containing the result you want returned.
- [if_not_found] – Optional text or value to display if no match is found.
- [match_mode] – Optional setting for exact or approximate matches.
- [search_mode] – Optional setting to control search direction.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> XLOOKUP Function Data
Basic XLOOKUP Formula
Suppose you have a product list and want to return the product price based on the product ID.
Formula Used
=XLOOKUP("P102",A2:A5,C2:C5)
How This Formula Works
- Excel searches for P102 in the Product ID column.
- Once found, it looks at the corresponding value in the Price column.
- The formula returns 25.
Looking Up Employee Departments
You can use XLOOKUP to return employee information quickly.
Formula Used
=XLOOKUP(E2,A2:A5,C2:C5)
How This Formula Works
- Excel searches for employee ID 1002.
- It finds the matching row.
- It returns the department value from column C.
Using IF_NOT_FOUND to Avoid Errors
One major advantage of XLOOKUP is built-in error handling.
Formula Used
=XLOOKUP(D2,A2:A4,B2:B4,"Not Found")
How This Formula Works
- Excel searches for P200.
- Since it does not exist, Excel normally returns an error.
- The “Not Found” message appears instead.
Looking Left with XLOOKUP
Unlike VLOOKUP, XLOOKUP can return values from columns to the left.
Formula Used
=XLOOKUP(D2,B2:B5,A2:A5)
How This Formula Works
- Excel searches for 1003 in the Employee ID Lookup column.
- It finds 1003’s row.
- XLOOKUP returns the Employee name from the column to the right.
Common XLOOKUP Mistakes
Using Different Sized Ranges
The lookup range and return range must contain the same number of rows.
Incorrect:
=XLOOKUP(A2,B2:B10,C2:C8)Correct:
=XLOOKUP(A2,B2:B10,C2:C10)Searching in the Wrong Column
Make sure your lookup_array contains the value you are searching for.
Forgetting the IF_NOT_FOUND Argument
Using if_not_found makes formulas cleaner and easier to understand.
Example:
=XLOOKUP(A2,B2:B10,C2:C10,"No Match")XLOOKUP vs VLOOKUP
Although both functions retrieve matching information, XLOOKUP is generally the better option in modern Excel.
| Feature | XLOOKUP | VLOOKUP |
|---|---|---|
| Search Direction | Left or right | Right only |
| Error Handling | Built in | Requires IFERROR |
| Column Insertions | Safe | Can break formulas |
| Exact Match Default | Yes | No |
| Simpler Syntax | Yes | Less flexible |
Why XLOOKUP Is Better
XLOOKUP removes many frustrations that users experienced with VLOOKUP. You do not have to count column numbers, formulas are easier to read, and it can search both left and right. It also includes built-in error handling, reducing the need for extra formulas.
If your version of Excel supports XLOOKUP, it is usually the better choice.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> XLOOKUP Function Data
