Learn how to use XLOOKUP in Excel with this step-by-step tutorial for beginners. Includes syntax, real-life examples, and comparisons with VLOOKUP and INDEX-MATCH.
Learn how to use XLOOKUP in Excel with this step-by-step tutorial for beginners. Includes syntax, real-life examples, and comparisons with VLOOKUP and INDEX-MATCH.
If you have ever struggled with VLOOKUP or INDEX-MATCH in Excel, then you’ll love XLOOKUP. Introduced in Excel 365 and Excel 2021, XLOOKUP is a modern and powerful function that makes searching and retrieving data much easier.
In this beginner-friendly step-by-step tutorial, you’ll learn what XLOOKUP is, how its syntax works, and see real-life examples that will help you master it quickly.
Table of Contents
Step 1: What is XLOOKUP in Excel?
XLOOKUP is a function that allows you to search for a value in a range (or array) and return a related value from another range. Unlike VLOOKUP, it works both left and right and eliminates the need to count column numbers.
Think of it as an upgraded, smarter version of VLOOKUP and HLOOKUP combined.
Step 2: Understand the Syntax of XLOOKUP
The syntax of XLOOKUP is:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Explanation of each part:
lookup_value → The value you want to find (e.g., a name or product code).
lookup_array → The range where Excel should look for the value.
return_array → The range from which Excel should return the result.
[if_not_found] → (Optional) Message or value to display if no match is found.
[match_mode] → (Optional) Allows exact match, approximate match, or wildcard search.
[search_mode] → (Optional) Lets you search top-to-bottom or bottom-to-top.
Step 3: Why XLOOKUP is Better Than VLOOKUP
Before diving into examples, let’s quickly compare:
- ✅ No column index needed (you don’t have to count columns).
- ✅ Works left and right (VLOOKUP only works right).
- ✅ Custom “Not Found” messages make your sheet more user-friendly.
- ✅ Supports exact, approximate, and wildcard matches.
- ✅ Replaces VLOOKUP, HLOOKUP, and INDEX-MATCH in one function.
In short: XLOOKUP saves time, avoids errors, and is much easier to use.
Step 4: Real-Life Examples of XLOOKUP in Excel
Example 1: Find an Employee’s Salary
You have an employee list like this:
| Employee Name | Department | Salary |
|---|---|---|
| John | HR | 45,000 |
| Priya | IT | 60,000 |
| Ahmed | Finance | 55,000 |
| Riya | Marketing | 50,000 |
Task: Find Priya’s salary.
=XLOOKUP("Priya", A2:A5, C2:C5, "Not Found")Result: 60,000
Example 2: Product Price Lookup
Your product list looks like this:
| Product Code | Product Name | Price |
|---|---|---|
| P001 | Laptop | 55,000 |
| P002 | Mouse | 800 |
| P003 | Keyboard | 1,200 |
| P004 | Monitor | 9,500 |
Task: Find the price of product code P004.
Formula:
=XLOOKUP("P004", A2:A5, C2:C5, "Not Available")Example 3: Handling Missing Data
XLOOKUP allows you to return a custom message when data is missing.
Formula:
=XLOOKUP("P010", A2:A5, C2:C5, "Product Not Found")Result: Product Not Found
This makes your worksheet look more professional than showing errors.
Example 4: Wildcard Search
If you only know part of a name, use wildcards:
Formula:
=XLOOKUP("*iya", A2:A5, C2:C5)Result: Returns salary for “Priya” or “Riya” (since both names end with “iya”).
Step 5: XLOOKUP vs INDEX-MATCH
Many advanced Excel users used INDEX-MATCH before XLOOKUP. Here’s a comparison:
INDEX-MATCH Formula:
=INDEX(C2:C5, MATCH("Priya", A2:A5, 0))XLOOKUP Formula:
=XLOOKUP("Priya", A2:A5, C2:C5)Clearly, XLOOKUP is shorter, cleaner, and easier to understand.
Step 6: Key Takeaways
1- XLOOKUP is available in Excel 365 and Excel 2021.
2- It replaces VLOOKUP, HLOOKUP, and INDEX-MATCH.
3- You can use it for exact, approximate, and wildcard matches.
4- Built-in “Not Found” messages make your sheet more professional.
5- Works both vertically and horizontally.
Conclusion
If you’re still relying on old formulas like VLOOKUP, it’s time to make the switch. XLOOKUP in Excel is more powerful, more flexible, and much easier to use.
With just a few simple steps, you can quickly find employees’ salaries, product prices, grades, or any other data you need without worrying about errors or column numbers.
So next time you open Excel, try XLOOKUP — it might just become your favorite function.
FAQs on XLOOKUP in Excel
Q1: Why can’t I find XLOOKUP in my Excel?
XLOOKUP is only available in Excel 365 and Excel 2021. If you’re using Excel 2016 or earlier, you won’t see i
Q2: Is XLOOKUP better than VLOOKUP?
Yes, XLOOKUP is faster, more flexible, and eliminates the limitations of VLOOKUP.
Q3: Can XLOOKUP search from bottom to top?
Yes, by using the [search_mode] argument, you can search from last to first.
Q4: Does XLOOKUP work with wildcards?
Yes, you can use * (asterisk) and ? (question mark) for partial matches.
