Options->Calc->Calculate has this setting: [ ] Search criteria = and <> must apply to whole cells The "= and <>" there is misleading / wrong: it looks like only the criterion with these operators is affected, while it applies to *any* criterion (see the "criterion" documentation added in bug 131995). The wrong piece must be removed from the text of the setting; and the help for the option [1] should refer to / use the documentation introduced in bug 131995. [1] https://help.libreoffice.org/24.2/en-GB/text/shared/optionen/01060500.html?DbPAR=CALC#hd_id3152581
The option is evaluated in case of Match_mode = 0 (exact match), but is ignored in case of Match_mode = 1 or Match_mode = -1 (approximate match) in XLOOKUP, for example. So simple removing = and <> does not solve the problem. Albeit I have no proposal.
Thanks Regina! IMO, removal will solve the problem. It is OK to not use very detailed wording, and let the documentation fill the gaps. But it's bad to have misleading wording :-)
Eike, do you have some opinion here?
It's for operators = and <>, also when used in D*() criteria, and when matching for equality like in LOOKUP() or HLOOKUP() and VLOOKUP() with mode=0 (not sorted range lookup), apparently same in XLOOKUP(), and for criteria in *IF() and *IFS() functions. All those also obey the setting if Wildcards or Regex are enabled. Maybe reword to Operators = and <> and search criteria for equality must apply to whole cells
Setting to "new" as something needs to be done. Heiko, opinion on the wording?
(In reply to Mike Kaganski from comment #0) > Search criteria must apply to whole cells (In reply to Eike Rathke from comment #4) > Operators = and <> and search criteria for equality must apply to whole cells (In reply to Eike Rathke from comment #4) > ...and for criteria in *IF() and *IFS() functions. Don't we have "Enable wildcards/regular expressions in formulas" for this? Anyway, how about simply saying "[ ] Enable wildcard in search"? Besides, such an option is weird and I'd expect to rather add wildcards on every search like *win* or "win".
(In reply to Heiko Tietze from comment #6) > (In reply to Mike Kaganski from comment #0) > > Search criteria must apply to whole cells > (In reply to Eike Rathke from comment #4) > > Operators = and <> and search criteria for equality must apply to whole cells > > (In reply to Eike Rathke from comment #4) > > ...and for criteria in *IF() and *IFS() functions. > Don't we have "Enable wildcards/regular expressions in formulas" for this? No, they are independent. > Anyway, how about simply saying "[ ] Enable wildcard in search"? Does not suit, as it is neither about wildcards nor about search. > Besides, such an option is weird and I'd expect to rather add wildcards on > every search like *win* or "win". You may find it weird, but the = and <> operators _never_ evaluate wildcards or regex.