XLOOKUP In Excel

XLOOKUP is an updated version of VLOOKUP, HLOOKUP and other Lookup functions in excel.
XLOOKUP searches for a value in table or array of values.

Syntax of XLOOKUP as follows:

=XLOOKUP(lookup,lookup_array,return_array,[not_found],[match_mode],[search_mode])

• lookup : The value which you want to lookup.
• lookup_array : The range in which lookup value is present.
• return_array : The range that contains the return value.
• not_found : Optionally, you can specify the value to return if match not found.
• match_mode : Optionally, you can specify 0(default) - exact match , -1 - exact or smaller , 1 - exact or larger.
• search_mode : Optionally, you can specify 1 to search from top to bottom and-1 to search from bottom to top.

Basic XLOOKUP Function

Result is as follows:

Not found

Like, other Lookup functions in excel XLOOKUP also returns N/A error when it can't find a match.
Unlike, other Lookup functions XLOOKUP supports an optional argument named not found to give a message rather than a N/A error.
Note: when providing message, text must be enclosed in double quotes("")

Result is as follows:

Match Mode

By default, XLOOKUP will perform an exact match but we change match type by using match mode argument.There are following arguments in match mode :

0(default) - exact match
1 - exact or next larger item
-1 - exact or next smaller item.
2 - wildcard match(*,?,~)

Result is as follows:

Search Mode

By deafault,XLOOKUP work from top to bottom but you can also change its behaviour by search mode argument.

1(default) - top to bottom
-1 - bottom to top(reverse)

Result is as follows:

Note : XLOOKUP function works only on Excel365.