Is match with part of cell possible by a setting?

Question

Does Excel have a setting so that there is a match in the functions XMATCH and XLOOKUP (and other such functions) even if the search string only partially matches the value in the lookup_array?

Example: Something so that ´XMATCH("Alex";$B$2:$B:$300)´ matches not only exact "Alex" but also "Alexia".

I know, that XMATCH("Alex*";$B$2:$B:$300;2) does this, but it cannot be combined with binary search, whereas XMATCH("Alex";$B$2:$B:$300;0;2) is possible.

OpenDocument Format as a global setting ´table:search-criteria-must-apply-to-whole-cell´ (true/false) for that.

Answer 1

Hi,

For XLOOKUP have the [match_mode], the 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.

https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.