XLOOKUP
The XLOOKUP function searches an array or range, and returns the item(s) corresponding to the first match it finds.
If no match exists, then XLOOKUP returns a #N/A .
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
The required arguments in english, ignoring the optional arguments in square brackets.
=XLOOKUP(What you want to find, The list to search, The list of values to return)
The simplified rules:
The lookup_value is usually a single value or cell, it can be a range.
The lookup_array and return_array must have the same number of rows.
The required arguments are as follows.
| Argument | Description | Type | Notes/values |
|---|---|---|---|
| lookup_value | The value to lookup | Required | If omitted, a blank cell is used for matching. Note: An empty string is not the same as a blank cell, it is not valid as a lookup_value, and will produce an error. |
| lookup_array | The array or range to search for the lookup_value | Required | The number of rows must match the return_array |
| return_array | The array or range to return when matched to the lookup_value | Required | The number of rows must match the lookup_array |
The following arguments are optional.
| Argument | Description | Type | Notes/values |
|---|---|---|---|
| [if_not_found] | Where a valid match is not found, the [if_not_found] text is returned. | Optional | If a valid match is not found, and the [if_not_found] setting is not used, #N/A is returned. |
| [match_mode] | Specify the match type to be used | Optional | 0 - Exact match. If nothing is found, return #N/A. This is the default. |
| -1 - Exact match. If nothing is found, return the next smaller item. | |||
| 1 - Exact match. If nothing is found, return the next larger item. | |||
| 2 - A wildcard match using *, ?, and ~ characters. | |||
| [search_mode] | Specify the search mode to be used | Optional | 1 - Perform a search starting with the first item. This is the default. |
| -1 - Perform a reverse search starting with the last item. | |||
| 2 - Perform a binary search. It relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned. | |||
| -2 - Perform a binary search. It relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned. |
Comments
XLOOKUP was introduced to Office 365 in July 2020.
It acts as a modern and flexible replacement for older functions like:
- LOOKUP
- VLOOKUP
- HLOOKUP
- INDEX and MATCH
Using XLOOKUP has multiple benefits.
- Easier syntax than VLOOKUP.
- Defaults to an exact match.
- Inserting columns in the source data does not cause invalid results to be returned.
- Can search and return values to the left.
- Vertical and horizontal lookups.
- Built in error handling for no results.
- Returns a range instead of a value.
- Can be used to do a two way match.
Best to use
- In most situations, use the XLOOKUP function instead of VLOOKUP, HLOOKUP, LOOKUP, INDEX AND MATCH.
- Combining a vertical and horizontal XLOOKUP to return an intersection point is very powerful use of the function.
Limitations
The function will always return the first match but can be made to return a range linked to the first match.
Tip
If the XLOOKUP function is not matching to data but the lookup_value appears in the lookup_array, check these things.
- The lookup_value and lookup_array need to be the same data format. A text lookup_value will not be matched to a numeric value in the lookup_array.
- Check that the lookup_value and lookup_array do not have mismatched leading or trailing spaces or non-printable characters. The TRIM and CLEAN functions can be used to clean up the data.
Examples
The simplest use of XLOOKUP, doing a lookup on arrays.
The usual use of XLOOKUP, doing a lookup on ranges.
