In XLOOKUP and XMATCH wildcard search does not work together with binary search

Question

In XLOOKUP and XMATCH wildcard search does not work together with binary search

Regina Henschel 286

In a spreadsheet with

Name	Amount
Carol	90
Christel	43
Christian	85
Christopher	27

the formulas =XLOOKUP("Christ*";A2:A6;B2:B6;;2;1) =XMATCH("Christ*";A2:A6;2;1) work. But formulas =XLOOKUP("Christ*";A2:A6;B2:B6;;2;2) =XMATCH("Christ*";A2:A6;2;2) are not accepted.

There is no need to exclude wildcard match from binary search and such restriction is not documented. I think it is a bug in Excel 365.

1 answer

Your answer

Answer 1

Jiajing Hua 18,065 Moderator

Hi @Regina Henschel

Here is a similar thread: xlookup with binary search.

Please do not do a Binary Search with a wildcard option.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.

Regina Henschel 286 Reputation points

2024-04-22T09:00:45.31+00:00

LibreOffice can use the combination of wildcard search with binary search, for example. I see no reason to restrict the user. If Excel is not able to do it, it could automatically do a linear search internally.
Jiajing Hua 18,065 Reputation points Moderator

2024-04-23T05:25:14.6233333+00:00

Hi @Regina Henschel

Thanks for your back.

For your concerns, it's recommended to submit it to Microsoft Feedback Forum: https://feedbackportal.microsoft.com/feedback/forum/c23f3b77-f01b-ec11-b6e7-0022481f8472

This way, you can help Microsoft engineers have a better visibility on what issues that you’re experiencing with Office product and which features are most important to you.

Thanks for your understanding.

Share via

In XLOOKUP and XMATCH wildcard search does not work together with binary search

1 answer

Your answer