Xlookup in Excel is the most powerful function for any spreadsheet. It is a substitute for Microsoft Excel lookup function. That is a powerhouse. Now xlookup function in Excel helps users manage data, analyze numbers, and automate tasks.
I would like to tell you. One of the most important things Excel users often do is look up data from tables. For years, we’ve relied on functions like VLOOKUP, HLOOKUP, and INDEX+MATCH. But now, there’s a modern and powerful alternative as XLOOKUP.
It was introduced in 2019 and is available in Microsoft 365 and Excel 2021+. XLOOKUP replaces all the old lookup formulas with a more intuitive, flexible, and powerful solution. I will make it a quick and understandable XLOOKUP formula with an example.
In this article, you’ll learn:
- What is XLOOKUP
- How it works
- Syntax breakdown
- Real-world examples
- Comparison with VLOOKUP & INDEX-MATCH
- Common errors and solutions
- Advanced uses
- Why it’s a game-changer
What is XLOOKUP in Excel?
XLOOKUP is a lookup function in Excel that lets you find things in a table or range — by row or column. It’s designed to replace older lookup functions like:
- VLOOKUP (vertical)
- HLOOKUP (horizontal)
- LOOKUP
- INDEX + MATCH
🔥 Key Benefits:
- Works left-to-right AND right-to-left
- Returns exact matches by default (no need to set FALSE like in VLOOKUP)
- Can return multiple results
- Handles missing values with customizable messages
- Supports approximate match, wildcards, and reverse search
XLOOKUP Syntax Explained
excel
CopyEdit
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Parameter | Description |
| lookup_value | The value you want to look for |
| lookup_array | The range/column where to search for the value |
| return_array | The range/column to return a result from |
| if_not_found (optional) | What to show if no match is found |
| match_mode (optional) | 0 = exact match (default), -1 = next smaller, 1 = next larger, 2 = wildcard |
| search_mode (optional) | 1 = first to last (default), -1 = last to first |
Basic Example
🧾 Scenario:
You have a table of employees and their departments.
| Name | Department |
| John | Sales |
| Priya | Marketing |
| Ahmed | Finance |
| Sarah | HR |
👉 Task:
Find which department Sarah belongs to.
excel
CopyEdit
=XLOOKUP(“Sarah”, A2:A5, B2:B5)
✅ Output: HR
XLOOKUP vs. VLOOKUP
Let’s compare the two:
| Feature | VLOOKUP | XLOOKUP |
| Match direction | Only left-to-right | Both directions |
| Default match | Approximate | Exact |
| Column number needed? | Yes | No |
| Can return an array/multiple columns | No | Yes |
| Can handle errors without IFERROR | No | Yes |
| Supports wildcards | Yes | Yes |
| Available in | All versions | Excel 365 & Excel 2021+ |
🔁 Example Comparison
Using VLOOKUP:
excel
CopyEdit
=VLOOKUP(“Sarah”, A2:B5, 2, FALSE)
Using XLOOKUP (cleaner):
excel
CopyEdit
=XLOOKUP(“Sarah”, A2:A5, B2:B5)
Advanced Use Cases of XLOOKUP
✅ 1. Custom Message if Not Found
excel
CopyEdit
=XLOOKUP(“Mike”, A2:A5, B2:B5, “Not Found”)
If “Mike” is not found, it returns “Not Found” instead of #N/A.
✅ 2. Lookup with Wildcards
Example:
Find a department for a name that starts with “A”
excel
CopyEdit
=XLOOKUP(“A*”, A2:A5, B2:B5, , 2)
Here, “A*” means names starting with “A”, and 2 enables wildcard mode.
✅ 3. Reverse Search (last match)
excel
CopyEdit
=XLOOKUP(“John”, A2:A10, B2:B10, , 0, -1)
This searches from bottom to top (last to first).
✅ 4. Return Multiple Values
Let’s say you want to return the department and the joining date for a person.
| Name | Department | Joining Date |
| Priya | Marketing | 2022-01-10 |
excel
CopyEdit
=XLOOKUP(“Priya”, A2:A5, B2:C5)
This will return two values: Marketing and 2022-01-10.
Xlookup function in Excel
💼 1. Invoice System: Get Price by Product Name
| Product | Price |
| Pen | 10 |
| Notebook | 30 |
| Mouse | 500 |
excel
CopyEdit
=XLOOKUP(“Mouse”, A2:A4, B2:B4)
➡ Output: 500
🧑💼 2. Employee ID Lookup
| Emp ID | Name |
| 101 | Rahul |
| 102 | Meera |
| 103 | Akash |
excel
CopyEdit
=XLOOKUP(102, A2:A4, B2:B4)
➡ Output: Meera
Error Handling in XLOOKUP
Unlike VLOOKUP (where you’d need to wrap in IFERROR), XLOOKUP handles errors natively.
🛠 Example:
excel
=XLOOKUP(“XYZ”, A2:A10, B2:B10, “Not Available”)
Copyedit
If “XYZ” isn’t found, Excel will return “Not Available” instead of #N/A.
Nested XLOOKUP
You can combine XLOOKUP with other functions like SUM, IF, etc.
🧮 Example: Dynamic Price Lookup + Quantity
| Product | Price |
| Pen | 5 |
| Book | 50 |
If someone ordered 3 pens:
excel
CopyEdit
=XLOOKUP(“Pen”, A2:A3, B2:B3) * 3
➡ Output: 15
Common XLOOKUP Mistakes
| Mistake | Solution |
| Using incompatible arrays | Ensure lookup_array and return_array have the same size |
| Forgetting if_not_found | Always add a fallback for cleaner outputs |
| Wildcards not working | Set match_mode to 2 |
| Not available in Excel | XLOOKUP only works in Excel 365/2021+ |
Tips & Tricks
- Use dynamic ranges with Excel Tables (Ctrl + T) to auto-expand.
- Combine XLOOKUP with FILTER or SORT for advanced dashboards.
- Use LET + XLOOKUP to optimize complex formulas.
- In Google Sheets, XLOOKUP is now supported (as of 2023).
When NOT to Use XLOOKUP
While XLOOKUP is powerful, in large datasets (100k+ rows), it may be slightly slower than INDEX+MATCH in older Excel versions.
Also, for backward compatibility with Excel 2016 or earlier, use INDEX+MATCH.
Conclusion: Should You Use XLOOKUP?
Absolutely — if you’re using Excel 365 or Excel 2021+, XLOOKUP is the future. It’s clean, powerful, and far more flexible than older lookup formulas.
Whether you’re a beginner learning Excel or a professional building dynamic dashboards, mastering XLOOKUP will save you hours of work and reduce errors.
Bonus: XLOOKUP Cheatsheet
excel
Copyedit
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Match Mode | Description |
| 0 | Exact (default) |
| -1 | Exact or next smaller |
| 1 | Exact or next larger |
| 2 | Wildcard match |
| Search Mode | Description |
| 1 | Top to bottom (default) |
| -1 | Bottom to top |
FAQs
❓Can I get a job after learn XLOOKUP?
Yes! You can get a professional job after getting complete knowledge about Xlookup.
❓ Can I use XLOOKUP for multiple columns?
Yes! Just select multiple columns in the return_array.
❓ Is XLOOKUP available in Google Sheets?
Yes, Google Sheets added support for XLOOKUP in late 2023.
❓ Is XLOOKUP faster than VLOOKUP?
In general, yes — and it’s more powerful too. But for very large datasets, INDEX+MATCH may still be slightly