XMATCH, XLOOKUP: Remove dependency to 'search-criteria-must-apply-to-whole-cell'

[Regina Henschel <rb.henschel@xxxxxxxxxxx>]

Hi all, hi Balázs,

In current implementation of XLOOKUP and XMATCH the global setting 
'search-criteria-must-apply-to-whole-cell' is evaluated in Match_mode 
values 0 (exact match) and 2 (wildcard or regex). And the combination of 
Match_mode value 0 with Search_mode 2 or -2 (binary search) is possible 
when 'search-criteria-must-apply-to-whole-cell' has value false.

(1) I see a problem with 
'search-criteria-must-apply-to-whole-cell'=false together with binary 
search. How could that work? If you look for "foo" for example in this 
mode it would be the same as if you look for "*foo*" in wildcard mode 
and for that binary search is not possible. It there some hidden 
automatically switch to linear search in place?

(2) As far as I know, there exists no similar setting in Excel and 
evaluating the setting in LibreOffice gives interoperability problems.

(3) The setting is not needed, because the intended behavior of matching 
a part of the cell can be archived in wildcard mode with e.g. "*foo*" 
and in regex mode with e.g. ".*foo.*".

So my suggestion is, to take the chance when defining these _new_ 
functions to remove the evaluation of the global setting 
'search-criteria-must-apply-to-whole-cell' and act always as if its 
value is true.

What do you think?

[Background is, that the ODF TC is currently working on the 
specification for these functions.]

Kind regards,
Regina