Share via

Is match with part of cell possible by a setting?

Regina Henschel 151 Reputation points
2024-06-17T23:33:54.0466667+00:00

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.

Office
Office
A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.
1,421 questions
Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,631 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Emi Zhang-MSFT 23,271 Reputation points Microsoft Vendor
    2024-06-18T07:48:13.2866667+00:00

    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.