Excel XLOOKUP Function
The Excel XLOOKUP function performs lookups in vertical or horizontal ranges. It is a more robust and flexible successor to older functions like VLOOKUP, HLOOKUP, and LOOKUP. XLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches.
- lookup_value - The lookup value.
- lookup_array - The array or range to search.
- return_array - The array or range to return.
- match_mode - [optional] 1 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match.
- search_mode - [optional] 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending.
The Excel XLOOKUP function is a lookup function to retrieve data from a table of information. XLOOKUP can perform lookups in vertical or horizontal ranges, and is meant to be a more flexible and powerful successor to older functions like VLOOKUP, HLOOKUP, and LOOKUP. XLOOKUP supports both approximate and exact matches, and wildcards (* ?) for partial matches. In addition, XLOOKUP can search data starting from the first value or the last value (reverse lookup). See match type and search mode details below.
Advantages over VLOOKUP
XLOOKUP offers several important advantages over VLOOKUP:
- XLOOKUP can lookup data to the right or left of lookup values
- XLOOKUP can return results from more than one column (as in the example)
- XLOOKUP defaults to an exact match (VLOOKUP defaults to approximate)
- XLOOKUP can work with vertical and horizontal data
- XLOOKUP can search in reverse order
- XLOOKUP returns a range instead of just a value
The forth argument for XLOOKUP is match_type. This is an optional argument that controls match behavior as follows:
|0 (default)||Exact match. Will return #N/A if no match.|
|-1||Exact match or next smaller item.|
|1||Exact match or next larger item.|
|2||Wildcard match (*, ?, ~)|
The fifth argument for XLOOKUP is search_mode. This is an optional argument that controls search behavior as follows:
|1 (default)||Search from first value|
|-1||Search from last value (reverse)|
|2||Binary search values sorted in ascending order|
|-2||Binary search values sorted in descending order|
Binary searches are very fast, but take care data is sorted as required. If data is not sorted properly, a binary search can return invalid results that look perfectly normal.
Example #1 - multiple results
In the example shown, XLOOKUP is used to retrieve employee information based on an ID entered in B6. The formula in C6 is:
=XLOOKUP(B6,B9:B16,C9:F16) // all columns
Notice the result_array is C9:F16 , so all data in columns C through F is returned with one formula entered in C6. The data "spills" into the range C6:F6 automatically.
Example #2 - single result
To return just one value, adjust the return array as needed:
- XLOOKUP can work with both vertical and horizontal arrays.
- XLOOKUP will return #N/A if the lookup value is not found
- The lookup_array must have a dimension compatible with the return_array argument, otherwise XLOOKUP will return #VALUE!
- If XLOOKUP is used between workbooks, both workbooks must be open, otherwise XLOOKUP will return #REF!.