The XLOOKUP function can return results for exact matches as well as for approximate matches.
This is the fifth argument of the XLOOKUP.la.
If you do not specify the match mode, the default value of 0 will be considered.
As a result, the function will return only the exact match.
-1 and 1 are very useful when you have thresholds.
-1 will return the exact match or the next smaller matching value.
1 will return the exact match or the next larger matching value.
To get a partial match lookup value, enter 2 for the match mode.
Then, you can do a wildcard character match.
? (Question Mark ) -This wildcard match is for any single character.
* (Asterisk mark) – This wildcard match is for any number of characters.
The below 2 example shows the XLOOKUP formula where the lookup value is given with wildcard match mode.
Let’s match product codes that start with the letter “B”.
Unlike the VLOOKUP function, the XLOOKUP function in Excel has 4 search mode options 😯
We can search a lookup value starting from the first value or the last matching value.
You can even perform a binary search for the lookup value.
The below example spreadsheet shows the XLOOKUP function with search lookup value first-to-last.
We want to get product codes starting with the letter “A”.
The below image shows the same example with the search mode -1 which is the search lookup value last-to-first value.
You can enter 2 for search mode and do a binary search from the first-to-last matching value if you have sorted the lookup array in ascending order.
You can enter -2 for search mode and do a binary search from the last-to-first matching value if you have sorted the lookup array in descending order.