Excel XLOOKUP function

Function XLOOKUP

Description The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match.

Syntax XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

value The value to search for in the lookup_array.

lookup_array The array or range of cells to search for value.

return_array The array or range of cells from which a corresponding value will be returned, based on the position of value in lookup_array.

if_not_found Optional. The value to return if a match is not found. If this parameter is omitted, the function will return the #N/A error (similar to the VLOOKUP and HLOOKUP functions).

If a valid match is not found, and [if_not_found] is missing, #N/A is returned.

match_mode Optional. It is the type of match to perform. It can be one of the following values:

0 – Exact match. If none found, return #N/A. This is the default.

-1 – Exact match. If none found, return the next smaller item.

1 – Exact match. If none found, return the next larger item.

2 – A wildcard match where *, ?, and ~ have special meaning.

search_mode Optional. It is the type of search to perform. It can be one of the following values:

1 – Perform a search starting at the first item. This is the default.

-1 – Perform a reverse search starting at the last item.

2 – Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.

-2 – Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.

Examples:

Vertical lookup example.

Horizontal lookup example.