How to match exact data in XLOOKUP

The xlookup function in its default mode finds the data as exact match.

In this case, the match mode is in position 0.

=XLOOKUP(A2,A5:A14,B5:B14,,0)----->>>>answer is  22

How to match exact data (next smaller item)?

In this case, the match mode is in position -1. If nothing is found, it will show the next smaller item.

=XLOOKUP(A2,A6:A14,B6:B14,,-1)----->>>>answer is  9%

How to match exact data (next larger item)?

In this case, the match mode is in position -1. If nothing is found, it will show the next larger item.

=XLOOKUP(A2,A6:A14,B6:B14,,+1)----->>>>answer is  12%

How to match exact data (by wild card match)?

You can find the answer by searching a part of the desired data. In the below example, we search for the last three letters of the word along with *.

=XLOOKUP("*jah",A5:A14,B5:B14,,2)----->>>>answer is  22

In the example below, we search for the first three letters of the word along with *.

=XLOOKUP("Eli*",A5:A14,B5:B14,,2)----->>>>answer is  22

In the example below, we search for the middle three letters of the word along with *.

=XLOOKUP("*ija*",A5:A14,B5:B14,,2)----->>>>answer is  22

XLOOKUP function

XLOOKUP function searches a range or an array for a match and returns the corresponding item from a second range or array.

XLOOKUP arguments 

XLOOKUP(Lookup value,Table_array,Return_array,If_not_found,Match_mode)

This function has just 5 Arguments:

1. Lookup value is the value to search for.
2. Table_array is the array or range to search.
3. Return_array is the array or range to return.
4. If_not_found is returned if no match is found.
5. Match_mode specifies how to match lookup_value against the values in lookup_array.

Match_mode and If_not_found arguments of XLOOKUP function are optional.