⌨ Syntax
XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])
Arguments
lookup_value
(required)
— The value to search for *If omitted, XLOOKUP returns blank cells it finds in lookup_array .
lookup_array
(required)
— The array or range to search
return_array
(required)
— The array or range to return
if_not_found
(optional)
— Where a valid match is not found, return the [if_not_found] text you supply. If a valid match is not found, and [if_not_found] is missing, #N/A is returned.
match_mode
(optional)
— Specify the match type: 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)
— Specify the search mode to use: 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
=XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))
=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])