How to match exact data in XLOOKUP (with Example)

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

Table of Contents

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%

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%

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 searches a range or an array for a match and returns the corresponding item from a second range or array.

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

This function has just 5 Arguments:

Lookup value is the value to search for.
Table_array is the array or range to search.
Return_array is the array or range to return.
If_not_found is returned if no match is found.
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.