Re: [libreoffice-design] Re: behavior of new functions XLOOKUP and XMATCH

Hi Heiko,

I write to you directly too, because I don't know whether the design 
list allows attachments.

Heiko Tietze schrieb am 14.05.2024 um 08:21:
> Correct me but
>
> =XLOOKUP(C3;A$1:A$3;B$1:B$3;"INV";_3_;1) returns #VALUE! (Excel) or

The parameter Match_mode can only have the values
 0   exact match =,
 1   approximate match >=,
-1   approximate match <= and
 2   wildcard or regex match.
Value 3 is invalid.
Whether wildcard or regex is used, depends on the attributes 
table:use-regular-expressions and table:use-wildcards of the 
<table:calculation-settings> element. That are the radio buttons in 
Tools > Options > Calc > Calculate > Formulas Wildcards.

> Err:504 (LO)
> =_Y_LOOKUP(C4;A$1:A$3;B$1:B$3;"INV";0;1) returns #NAME? (both)

There is no function YLOOKUP. The old versions are VLOOKUP and HLOOKUP.

> What exactly will change?

I have attached the current state of the specification.

XLOOKUP function has more values for Match_mode and Search_mode than 
VLOOKUP and HLOOKUP.
XLOOKUP can work on separate arrays for LOOKUP and DATA, whereas VLOOKUP 
and HLOOKUP work on a rectangular range.
 ...and some more features.

The Excel help has some examples:
https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929

The new function XLOOKUP has a parameter Search_mode. Its values can be
1   linear search from first item to last item
-1  linear search from last item to first item
2   binary search on an ascended sorted array
-2  binary search on an descended sorted array

The problem is, what should be the output, when the user sets Match_mode 
2 together with Search_mode 2 or -2.

Kind regards,
Regina

> PS: ux-advice@ is the forward mailing list from Bugzilla and I'm not 
> sure how many people have registered. Rather use design@ for input from 
> the UI/UX group.
>
> On 14.05.24 12:23 AM, Regina Henschel wrote:
> > Hi UX-experts,
> >
> > the new function XLOOKUP and XMATCH have a Match_mode with values 0, 
> > 1, -1, 2 and a Search_mode with values 1, -1, 2, -2.
> > The Match_mode 2 means wildcard mode, that is ? * search or regular 
> > expressions search. The Search_modes 2 and -2 mean binary search in a 
> > sorted array.
> >
> > Combining parameter values for wildcard mode and binary search is 
> > possible. But there exist no way to actually do it. Excel gives an 
> > error messages in such cases. LibreOffice silently switches to linear 
> > search.
> >
> > The ODF TC is currently working on the specification for these new 
> > functions. The TC could either follow Excel's way or LibreOffice's 
> > way. From a developer state of view it is no large effort to change 
> > the current behavior of LibreOffice. The TC does not know yet whether 
> > Microsoft would be willing to switch to LibreOffice's behavior.
> >
> > I write to you for to get your opinion what will be the best solution 
> > from a _user_ point of view.
> >
> > Some arguments so far:
> > * No error message would make use of the functions in macros and forms 
> > easier.
> > * No error message might be better for using the functions in 
> > automatically calculations without UI.
> > * Using linear search can be unexpected slow on a huge Lookup_array. 
> > An error message makes the problem visible to the user.
> >
> > I would love to hear your opinion.
> >
> > Kind regards,
> > Regina


--
To unsubscribe e-mail to: design+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/design/
Privacy Policy: https://www.documentfoundation.org/privacy