| Syntax |
| =XLOOKUP(lookup_value,lookup_array,result_array,[if_not_found],[match_mode],[search_mode]) |
| Purpose / Description |
| Matches a value in a lookup array and returns the entry in a corresponding position from the result array. This is a massively improved lookup that allows an override of results when a match is not made, control of how matches are made and the order and how the search range is searched. Together with some performance improvements, this is a big improvement over the older lookup functions. |
| Arguments |
| Name |
Type |
Description |
| lookup_value |
any |
A constant, reference or a nested expression that returns the value to be found. |
| lookup_array |
reference or
array of any |
A constant array, reference to a range, or a nested expression that returns a reference to a range or an array of values, within which the match will be made. |
| result_array |
reference or
array of any |
A constant array, reference to a range, or a nested expression that returns a reference to a range or an array of values, from which the match the result of the calculation will be retrieved. |
| [if_not_found] |
any |
An optional constant, reference to a range, or a nested expression that returns value that should be returned if a match is not made. If omitted, the default value returned for a non-match is #N/A. |
| [match_mode] |
number |
An optional constant, reference or nested expression that returns the flag for the type of match to be performed. Defaults to 0 if omitted.
|
Exact Match, data order is irrelevant.
The match is made on the first entry in the searched range of the same value type that exactly matches lookup_value. |
| -1 |
Threshold Match, can work on both sorted and unsorted data. Relevance of sort order depends upon the search_mode argument.
The match is made with largest value, less than or equal to the lookup_value. If there are duplicates, whether the match is on first or last depends upon the search_mode argument. |
| 1 |
Threshold Match, can work on both sorted and unsorted data. Relevance of sort order depends upon the search_mode argument.
The match is made with smallest value, greater than or equal to the lookup_value. If there are duplicates, whether the match is on first or last depends upon the search_mode argument. |
| 2 |
Wildcard searching, allows wildcard matching on text strings, which is not permitted in other match_modes. |
|
| [search_mode] |
number |
An optional constant, reference or nested expression that returns the flag for the type of search to be performed. Defaults to 1 if omitted.
| 1 |
Exhaustive search, entry by entry from first to last. Sort order of data is irrelevant. |
| -1 |
Exhaustive search, entry by entry from last to last. Sort order of data is irrelevant. |
| 2 |
Binary search, sampling entries. Data must be sorted in Ascending Order for the results to be valid and reliable. |
| -2 |
Binary search, sampling entries. Data must be sorted in Descending Order for the results to be valid and reliable. |
|
| Return Values |
| Type |
Specific Value |
Description |
| reference |
|
If the result_array is a reference to a range (even if lookup_array isn't), and a match is made, then a reference to the matching entry is returned. |
| any |
|
if result_array is an array and a match is made, the entry that corresponds by position with the entry matched in lookup_array is returned. If no match is made, and if_not_found is given, then the value of if_not_found is returned. |
| error |
#N/A |
If no match is found and if_not_found is omitted, this value is returned. |
| error |
|
Any errors referenced by arguments are returned due to propagation of error values. |
| Notes / Comments / Advice / Warnings |
| Note: |
The value type of the lookup_value is important. As the argument is specified as "any", it will match numbers, text strings and logicals, but only against the same value types. A text_string will notmatch against a number and vice versa. |
| Advice: |
Replace all reference forms of the HLOOKUP and VLOOKUP function with the improved XLOOKUP. It allows exact matching (the default), and can work with ascending and descending sorted ranges, and do exhaustive search where data is unsorted. It also has built-in error handling for when no match is made. With the advent of XLOOKUP, we have effectively ceased using LOOKUP, HLOOKUP and VLOOKUP altogether. |
| Advice: |
To get get exactly the same answers as were achieved with the old LOOKUP function, a specific combination of match_mode and search_mode is required. match_mode should be set to -1and search_mode should be set to 2
To get exactly the same answers as were achieved with the old HLOOKUP or VLOOKUP functions, a specific combination of match_mode and search_mode is required.
Settings for HLOOKUP / VLOOKUP
|
Settings for XMATCH |
| range_lookup |
TRUE |
|
match_mode |
-1 |
search_mode |
2 |
| range_lookup |
FALSE |
|
match_mode |
|
search_mode |
1 or -1 |
|
| Caution: |
XLOOKUP matches on empty cells, so some care and changes will be required if migrating from older LOOKUP, HLOOKUP and VLOOKUP functions that did not have this behaviour. |
| Examples: |
Open / download sample workbook: XLOOKUP.xlsx |
| |