We’ve previously covered how we can combine INDEX and MATCH functions in order to “look up” data.
The XLOOKUP function works much the same way as the INDEX MATCH combination. It is intended to replace (still wildly used) legacy functions with outdated syntax and limited capabilities, such as VLOOKUP, HLOOKUP, and LOOKUP, while retaining accessibility.
The XLOOKUP function searches a range or an array for a match and returns the corresponding item from a second range or array.
The syntax of the XLOOKUP function is as follows:
= XLOOKUP ( lookup_value ; lookup_array ; return_array ; if_not_found ; match_mode ; search_mode )
The lookup value can be either a text or a number, inputted directly into the function or as a cell reference.
The lookup array represents an array or a range of cells (examples we will be using) to search.
The corresponding return array represents an array or a range of cells (examples we will be using) to return values from.
If we need to retrieve data from a single column, we can perform a simple vertical lookup using the XLOOKUP function:
If we need to retrieve data from a single row, we can perform a simple horizontal lookup using the XLOOKUP function:
Optional if not found filed will be returned when a valid match is not found.
If there is no valid match, and if not found part of the formula is missing, the #N/A error will be returned.
If there is no match type specified, the default 0, exact match, will be used.
Supported match types are:
- 0, exact match, if none is found, the #N/A error will be returned,
- -1, “exact” match, if none is found, the next smaller item will be returned,
- 1, “exact” match, if none is found, the next larger item will be returned,
- 2, a wildcard match where partial matches with the help of an asterisk *, a question mark ? or a tilde ~ are also supported.
When using a partial match, with an asterisk * we can match any sequence of characters, and with a question mark ? we can match any single character:
They can be combined, i.e., criteria such as “*8??” are valid.
If we need to match an actual asterisk * or a question mark ?, we can do this by placing a tilde ~ in front of them.
Next smaller and next larger match types can be used when looking up numbers, and with the XLOOKUP function, there are no requirements for the data to be presorted in any way. See this article on how (and when) to look up the next smaller item or the next larger item.
If there is no search mode specified, the default 1, search starting at the first item, will be used.
Supported search modes are:
- 1, a search starting at the first item,
- -1, a reverse search starting at the last item,
- 2, a binary search that requires the lookup array to be sorted in ascending order to function properly,
- -2, a binary search that requires the lookup array to be sorted in descending order to function properly.
If we designate the whole table as a return array (but still corresponding to the lookup array), our results will spill to the cells to the right:
In this example, as the cells E8:I8 were matched, they were returned to the cells C12:G12.
Instead of spilling our results into neighboring cells, we can also nest this return into different functions.
If we nest this return inside the SUM function, we will return a sum of the values in the cells E8:I8:
If we nest this type of return inside of another XLOOKUP function, we can replicate the functionality of the INDEX MATCH MATCH combination.
I.e., we can perform both vertical and horizontal lookups at the same time by nesting one XLOOKUP function inside another XLOOKUP function.
If we need to perform a lookup with multiple criteria, see how to match multiple criteria in multiple columns.
For other interesting applications, see how to combine SUMIFS with XLOOKUP in order to sum all of the values that meet multiple criteria in different rows and columns. See also, how to combine FILTER with XLOOKUP in order to filter and/or sum values based on criteria that are not present in the table you are returning values from.
Dig deeper:
Lookup with unique identifiers
How to look up the next smaller item or the next larger item
11 thoughts on “XLOOKUP function”