If you’ve ever worked with Excel, you’ve probably used functions like VLOOKUP or HLOOKUP to find data. But did you know there’s a more powerful and flexible alternative? Meet XLOOKUP. It’s a game-changer for data searches, offering a simpler syntax and removing many of the limitations of its predecessors.
What Is XLOOKUP?
XLOOKUP is a versatile lookup function introduced in Excel 365 and Excel 2019. Unlike VLOOKUP and HLOOKUP, it allows searches in both vertical and horizontal arrays, works naturally from left to right or right to left, and eliminates the dreaded “approximate match” issue.
XLOOKUP Syntax
The basic syntax of XLOOKUP is:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])- lookup_value – The value you’re searching for.
- lookup_array – The range where you’re searching for
lookup_value. - return_array – The range from which to return the result.
- [if_not_found] (optional) – A value to return if the lookup value isn’t found.
- [match_mode] (optional):
- 0 – Exact match (default)
- -1 – Exact match or next smallest
- 1 – Exact match or next largest
- 2 – Wildcard search
- [search_mode] (optional):
- 1 – Search from first to last (default)
- -1 – Search from last to first
- 2 – Binary search (ascending order needed)
- -2 – Binary search (descending order needed)
Basic Example of XLOOKUP
Let’s start with a simple example. Suppose we have the following table:
| Product | Price |
|---|---|
| Apple | $1.00 |
| Banana | $0.50 |
| Cherry | $2.00 |
To find the price of a Banana, we use:
=XLOOKUP("Banana", A2:A4, B2:B4)Excel will return 0.50.
XLOOKUP vs VLOOKUP
Why use XLOOKUP instead of VLOOKUP? Here’s a quick comparison:
| Feature | XLOOKUP | VLOOKUP |
|---|---|---|
| Search Direction | Both left & right | Only right |
| Exact Match Default | Yes | No |
| Handles Missing Values | Yes | No |
Using XLOOKUP with a Default Value
Normally, when a lookup value isn’t found, Excel returns an error. But with XLOOKUP, we can specify a default value:
=XLOOKUP("Orange", A2:A4, B2:B4, "Not Found")Since “Orange” doesn’t exist in the table, Excel will return “Not Found” instead of an error.
How to Perform a Reverse Lookup
Unlike VLOOKUP, XLOOKUP allows searching from right to left. Suppose our data is structured differently:
| Price | Product |
|---|---|
| $1.00 | Apple |
| $0.50 | Banana |
| $2.00 | Cherry |
We can find which product costs $0.50 with:
=XLOOKUP(0.50, A2:A4, B2:B4)Unlike VLOOKUP, we don’t have to worry about the lookup column being in the second position.
Using XLOOKUP for Wildcard Searches
We can perform wildcard searches by setting match_mode to 2:
=XLOOKUP("*nana", A2:A4, B2:B4, "Not Found", 2)Since “Banana” matches “*nana”, it returns 0.50.
Conclusion
XLOOKUP is an incredibly powerful function that simplifies lookups in Excel. Whether you’re dealing with large datasets, missing values, or reverse lookups, it’s the most versatile lookup function available. By replacing older functions like VLOOKUP and HLOOKUP, it streamlines searches and reduces errors, making your spreadsheets more efficient and easier to maintain.
Other interesting article:
How HLOOKUP works in Excel? Best HLOOKUP examples