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

Hi List,

> 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.

So the fix for the error is "do not to combine the parameters wildcard 
and binary search" and thus use linear search instead of binary?
Or can the error be caused by the kind of data (so that the flag 
combination that fails in Excel makes sense with some data but not with 
other kinds of data?)

If the error always has the same kind of fix (do not combine these 
parameters) no matter what data it works on, it seems that an error 
message makes a lot of sense.

Jan


Am 15.05.2024 um 02:03 schrieb Regina Henschel:
> 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