The `XLOOKUP` function in Excel is a powerful and flexible lookup solution that can replace older functions like `VLOOKUP`, `HLOOKUP`, and `LOOKUP`. It allows you to search for a value in a range or array and return a corresponding value from another range or array. Here’s how to use it:
Basic Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Parameters
- lookup_value: The value you want to look for.
- lookup_array: The range or array where you want to search for the `lookup_value`.
- return_array: The range or array from which you want to retrieve the value.
- [if_not_found] (Optional): The value to return if no match is found. If omitted, and no match is found, Excel will return an `#N/A` error.
- [match_mode] (Optional): The match type:
- `0` – Exact match (default).
- `-1` – Exact match or next smaller item.
- `1` – Exact match or next larger item.
- `2` – Wildcard match.
- [search_mode] (Optional): The search mode:
- `1` – Search from first to last (default).
- `-1` – Search from last to first.
- `2` – Perform a binary search (lookup_array must be sorted in ascending order).
- `-2` – Perform a binary search (lookup_array must be sorted in descending order).
Example
Suppose you have the following table in Excel:
| A | B |
|———-|——-|
| Product | Price |
| Apple | 1.2 |
| Banana | 0.5 |
| Orange | 0.8 |
If you want to find the price of the “Banana”, use the following formula:
=XLOOKUP("Banana", A2:A4, B2:B4)
Advanced Example with _if_not_found_
Suppose you want to include a default message if the product isn’t found:
=XLOOKUP("Cherry", A2:A4, B2:B4, "Product not found")
Using Wildcards
If you want to find a product that starts with “Ba”:
=XLOOKUP("Ba*", A2:A4, B2:B4, "Product not found", 2)
Tips
- Unlike `VLOOKUP`, `XLOOKUP` can search both vertically and horizontally, so you don’t have to worry about the orientation of your data.
- `XLOOKUP` allows you to return values to the left or above the lookup array, something `VLOOKUP` cannot do.
- It can handle errors more elegantly with the `if_not_found` parameter, providing a custom message instead of the usual error values.
By using `XLOOKUP`, you get more functionality and flexibility than the traditional lookup functions in Excel.
Unlock Your Potential
Excel
Access
Power BI
