Bug 127293 - Add XLOOKUP function in Calc
Summary: Add XLOOKUP function in Calc
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: high enhancement
Assignee: Balázs Varga (allotropia)
URL:
Whiteboard: target:24.8.0
Keywords:
: 130359 (view as bug list)
Depends on:
Blocks: Excel-Functions
  Show dependency treegraph
 
Reported: 2019-09-02 14:46 UTC by Roman Kuznetsov
Modified: 2024-02-26 13:35 UTC (History)
36 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Roman Kuznetsov 2019-09-02 14:46:21 UTC
Description:
Add XLOOKUP function in Calc

https://support.office.com/en-us/article/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929

Steps to Reproduce:
-

Actual Results:
-

Expected Results:
-


Reproducible: Always


User Profile Reset: No



Additional Info:
-
Comment 1 Mike Kaganski 2019-10-28 07:53:21 UTC
See also accompanying XMATCH: https://support.office.com/en-us/article/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312
Comment 2 Julien Nabet 2020-02-02 08:26:27 UTC
*** Bug 130359 has been marked as a duplicate of this bug. ***
Comment 3 gmolleda 2020-02-26 12:19:43 UTC Comment hidden (spam)
Comment 4 Lenge 2020-08-16 14:33:57 UTC Comment hidden (spam)
Comment 5 Andy Blackton 2021-02-23 08:36:13 UTC Comment hidden (me-too)
Comment 6 documentfoundation 2021-07-14 13:28:34 UTC
Microsoft even recommends to use the new XLOOKUP function:

https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

> Try using the new XLOOKUP function, an improved version of VLOOKUP that
> works in any direction and returns exact matches by default, making it
> easier and more convenient to use than its predecessor.
Comment 7 Aron Budea 2021-08-19 23:07:32 UTC
Since this is an enhancement, please keep version as unspecified.
Comment 8 MichaelB 2022-01-03 15:48:49 UTC Comment hidden (me-too)
Comment 9 Nick Rayton 2022-01-04 09:49:53 UTC Comment hidden (me-too)
Comment 10 Winfried Donkers 2022-01-18 14:26:57 UTC
I'll have a go at it.
Comment 11 MichaelB 2022-07-04 15:18:25 UTC
Hi, what about this feature in the upcoming LO 7.4?
Comment 12 Eike Rathke 2022-07-04 16:20:29 UTC
No. There will be commit notifications and a whiteboard target once it got implemented.
Comment 13 gmolleda 2022-09-22 10:48:38 UTC Comment hidden (me-too)
Comment 14 Eike Rathke 2022-09-23 09:21:16 UTC
Meanwhile, just arrived:
https://blog.documentfoundation.org/blog/2022/09/23/lox365-extension-xlookup-and-more-for-libreoffice-calc/
https://github.com/goosepirate/lox365

Note I have no idea of the compatibility of the implementation nor what exact arguments it supports and behaviour it implements and this is neither an endorsement nor a discouragement, just a heads-up.
Comment 15 Winfried Donkers 2022-09-23 12:12:05 UTC
(In reply to Eike Rathke from comment #14)

Thanks for the heads-up, I will have a look at the extension.
Meanwhile I am continuing work on the implementation of XLOOKUP.
Comment 16 Winfried Donkers 2022-10-28 10:45:06 UTC
I just found out that whereas HLOOKUP, MATCH, LOOKUP and VLOOKUP return the last found identical search value, XLOOKUP returns the the first.
That'll mean some rework.
Comment 17 gmolleda 2022-10-28 12:49:28 UTC
Can you give an example? There are different search-types, and XLOOKUP also allows you to choose the match_mode.
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
match_mode:
0 - Exact match. If none found, return #N/A. This is the default.
-1 - Exact match. If none found, return the next smaller item.
1 - Exact match. If none found, return the next larger item.
2 - A wildcard match where *, ?, and ~ have special meaning.

 [search_mode]
Specify the search mode to use:
1 - Perform a search starting at the first item. This is the default.
-1 - Perform a reverse search starting at the last item.
2 - Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.
-2 - Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.
Comment 18 Mike Kaganski 2022-10-28 13:24:29 UTC
(In reply to gmolleda from comment #17)

https://1drv.ms/x/s!AqRfhRdisQhQg7pMU14AUX8vt2nA-g?e=zVFenh
Comment 19 gmolleda 2022-10-28 14:53:33 UTC
You could change the XLOOKUP to:
XLOOKUP(3,A1:A9,B1:B9,,-1,-1)
and the result will be the same that VLOOKUP.
Comment 20 Mike Kaganski 2022-10-28 15:17:43 UTC
(In reply to gmolleda from comment #19)

Indeed. But that is a different mode: we should compare *binary search* mode of VLOOKUP (4th "1") with *binary search* mode of XLOOKUP. Which is what Winfried (who is working on the implementation) is talking, because it's these details that must be taken into account when implementing a new function.
Comment 21 gmolleda 2022-10-28 15:36:44 UTC
(In reply to Mike Kaganski from comment #20)

Why do you think the fourth parameter in VLOOKUP means binary lookup?
That fourth parameter is simply whether the search is exact (exists or not) or approximate (to find an interval).
Comment 22 Mike Kaganski 2022-10-28 16:10:45 UTC
(In reply to gmolleda from comment #21)

ECMA-376 Part 1 18.17.7.343 VLOOKUP

> Syntax:
> VLOOKUP ( lookup-value , table-array , col-index-num [ , [ range-lookup-flag ] ] )
> ...
> table-array  array,     Designates the table of information to be searched. The
>              reference, values in the left-most column of table-array can be text,
>              name       numbers, or logical values. The values in the left-most
>                         column of table-array shall be placed in "ascending
>                         order", as follows: …, -2, -1, 0, 1, 2, …, A–Z, FALSE, TRUE.
>                         Uppercase and lowercase text is treated as equivalent.
> ...
> range-lookup logical    Specifies whether HLOOKUP is to find an exact or
> -flag                   approximate match. If TRUE or omitted, an approximate
>                         match is returned. That is, if an exact match is not found,
>                         the next largest value that is less than lookup-value is
>                         returned. If FALSE, an exact match is performed, in
>                         which case, the values in the left-most column of table-array
>                         need not be sorted. If there are two or more values
>                         in the left-most column of table-array that match lookup-value,
>                         the top-most value found is used.

The standard specifies that when TRUE, 'range-lookup-flag' requires that left-most column of table-array *must* be sorted ascending. The same requirement have value '2' of XLOOKUP.
Comment 23 gmolleda 2022-10-28 16:53:37 UTC
(In reply to Mike Kaganski from comment #22)
Do you think then VLOOKUP or XLOOKUP does not follow the standard?
I think the problem is that these searches are not designed so that there are several equal values, but to search in intervals:
0	a
7	b
12	c
15	d
b	=BUSCARV(9;A1:B4;2;1)
b	=BUSCARX(9;A1:A4;B1:B4;;-1;2)
Comment 24 gmolleda 2022-10-28 16:56:22 UTC
(In reply to gmolleda from comment #23)
BUSCARV --> VLOOKUP and BUSCARX --> XLOOKUP, and change ; to ,

b	=VLOOKUP(9,A1:B4,2,1)
b	=XLOOKUP(9,A1:A4,B1:B4,,-1,2)
Comment 25 Mike Kaganski 2022-10-28 17:06:46 UTC
(In reply to gmolleda from comment #23)
> Do you think then VLOOKUP or XLOOKUP does not follow the standard?

No. And all the discussion that you do here is likely because you misunderstand what Winfried wrote. No one asked for advises how to workaround one formula with another, or if something is designed for some task or not. Winfried found a specific property of the function, which needs to be implemented properly in Calc; and mentioned that this finding would increase time needed for implementation. Everything after was just cluttering the issue.
Comment 26 gmolleda 2022-10-29 08:45:37 UTC
(In reply to Mike Kaganski from comment #25)
Oh sorry, that needs more development.
Comment 27 Gerry 2023-02-18 21:00:02 UTC
This is the gerrit link, where Winfried is working on the patch: https://gerrit.libreoffice.org/c/core/+/131905
Comment 28 Winfried Donkers 2023-02-19 08:06:11 UTC
(In reply to Gerry from comment #27)
> This is the gerrit link, where Winfried is working on the patch:
> https://gerrit.libreoffice.org/c/core/+/131905

A word of caution here: that patch is currently far from complete and still has a lot of experimental code in it. It is meant for discussion between developers.
Comment 29 Winfried Donkers 2023-10-04 10:18:48 UTC
Due to personal circumstances, I am forced to quit working on this bug report.
The work (apart from various optimisations) the job is 80-90% finished.

I can provide additional information and some help to developers taking the implementation of XLOOKUP upon them.
Comment 30 Óvári 2023-11-03 10:43:39 UTC
(In reply to Winfried Donkers from comment #29)
> Due to personal circumstances, I am forced to quit working on this bug
> report.
> The work (apart from various optimisations) the job is 80-90% finished.
> 
> I can provide additional information and some help to developers taking the
> implementation of XLOOKUP upon them.

Will you, or someone, accept payment to get this feature implemented in LibreOffice Core?
Comment 31 Winfried Donkers 2023-11-03 13:16:17 UTC
It is possible to have a (certified) developer or company implement XLOOKUP.
This it not cheap, think in thousands of euros.
I am a voluntary developer; my abandoning of the implementation has health as cause, not the intention to get money.

Look at https://www.libreoffice.org/get-help/professional-support/ for options, I do not wan to push one or other fellow developer.
Comment 32 LBR 2023-11-28 11:03:53 UTC
Winfried

Firslt, can I say I am sorry to hear you have health issues.  I hope you get well soon and thank you on all your work on this to date

I am a user with no programming skills.  I am not sure how Libre Office prioritises these development issues. Can I check whether the fact you have had to end your invoolvement in this means it gets flagged and a decision will get taken on prioritisation for another developer to volunteer to take on?

e.g. does someone somewhere say "Oh, there's lots of demand for this. It's quite key because MS Excel docs and Google sheets which contain the now standard XLOOKUP won't transfer across to Libre Calc and so we assign this a priority of X?"

And then a volunteer developer looks at the list of high priority fixes and chooses whether they cna help?

i just don't understand how the process works?

Thanks again for all your help Winfried
Comment 33 Winfried Donkers 2023-11-30 13:26:15 UTC
(In reply to LBR from comment #32)
> i just don't understand how the process works?

There are several options as far as I know:
- a developer 'takes' a bug report to fix. This is entirely a free choice of the developer, but can be influenced by the Engineering Steering Committee (ESC);
- a user, company, organisation wants something done (a bug fix, an enhancement like a new function) and is prepared to pay for it. Certified developers and companies can provide this service (see comment #31);
- The Document Foundation TDF) want something done (mostly an enhancement or improvement) and writes out a tender for developers/companies to bid on. TDF then finances this enhancement/improvement. Naturally, the budget of the TDF is limited. Donations help, of course.
Comment 34 LBR 2023-12-06 08:38:26 UTC
Thanks. 

Unless TDF picks this up, I suspect it may be a little while.

Thanks for your work to date and I do hope you get well soon

I have actually found Google Sheets functionality has really come on in recent years, and I can use the XLOOKUP formula in there where I need that in meantime
Comment 35 Óvári 2023-12-10 01:18:31 UTC Comment hidden (off-topic)
Comment 36 Eike Rathke 2023-12-11 14:14:06 UTC Comment hidden (off-topic)
Comment 37 Balázs Varga (allotropia) 2024-01-11 15:38:57 UTC
Hi Winfried Donkers,

I tried to reply to your mail from my different mails, but it said all the time:
"I'm sorry to have to inform you that your message could not
be delivered to one or more recipients." - winfrieddonkers@libreoffice.org
Is this the correct one?
Comment 38 Gabor Kelemen (allotropia) 2024-01-31 01:52:59 UTC
I tested the proposed patch at https://gerrit.libreoffice.org/c/core/+/131905 with example files found on Excel tutorial sites, such as:

https://trumpexcel.com/xlookup-function/
direct link -> https://www.dropbox.com/s/g5q6y7lfd9y3ubz/XLOOKUP%20Examples.xlsx?dl=1 [0]

https://www.ablebits.com/office-addins-blog/excel-xlookup-function/
direct link -> https://cdn.ablebits.com/excel-tutorials-examples/excel-xlookup-function.xlsx

https://www.contextures.com/excelxlookupfunctionexamples.html
direct link -> https://www.contextures.com/functionsamples/excelxlookupexamplesbasic.zip https://www.contextures.com/functionsamples/excelxlookupexamplesadv.zip

https://exceljet.net/functions/xlookup-function
and its sub-pages:
https://exceljet.net/formulas/xlookup-wildcard-contains-substring
https://exceljet.net/formulas/xlookup-with-multiple-criteria
https://exceljet.net/formulas/xlookup-binary-search
https://exceljet.net/formulas/due-date-by-category
https://exceljet.net/formulas/lookup-lowest-monday-tide

https://chandoo.org/wp/xlookup-examples/
direct link -> https://chandoo.org/wp/wp-content/uploads/2019/09/xlookup-examples.xlsx [1]
https://chandoo.org/wp/wp-content/uploads/2020/05/wtf-xlookup.xlsx
https://chandoo.org/wp/wp-content/uploads/2021/03/xlookup-demo.xlsx

Mostly these seem to work now fine.

There is one XLOOKUP problem left in file [0], I'll report it separately.

Other exceptions are not due to the new XLOOKUP but other missing functions (SEQUENCE, FILTER) or table issues (bug 159341) in file [1].
Comment 39 Commit Notification 2024-01-31 08:15:08 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/f7039822c7ad3987326e1c20ea4a745c158f9682

tdf#127293 Add Excel2021 function XLOOKUP to Calc

It will be available in 24.8.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 40 Winfried Donkers 2024-01-31 13:21:54 UTC
(In reply to Commit Notification from comment #39)
> Winfried Donkers committed a patch related to this issue.

For 'Winfried Donkers' read 'Winfried Donkers and Balázs Varga'. Any compliments should go to Balázs.
Comment 41 Commit Notification 2024-02-08 07:25:45 UTC
Balazs Varga committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/68738bd0ac262819b13ea7e11af67ee493b9b3e1

Related: tdf#127293 Fix function XLOOKUP binary search corner cases

It will be available in 24.8.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 42 ady 2024-02-08 09:15:17 UTC
If I may ask...

Quote from 68738bd0ac262819b13ea7e11af67ee493b9b3e1

"
Fix some binary search (vertical) corner cases in case of XLOOKUP
where we looking for the first matches.
"

What is that "vertical" about? I mean, are "horizontal" corner cases also covered? (xlookup is supposed to be able to replace not only vlookup but hlookup too.)
Comment 43 Balázs Varga (allotropia) 2024-02-08 10:05:17 UTC
(In reply to ady from comment #42)
> If I may ask...
> 
> Quote from 68738bd0ac262819b13ea7e11af67ee493b9b3e1
> 
> "
> Fix some binary search (vertical) corner cases in case of XLOOKUP
> where we looking for the first matches.
> "
> 
> What is that "vertical" about? I mean, are "horizontal" corner cases also
> covered? (xlookup is supposed to be able to replace not only vlookup but
> hlookup too.)

It means when we are looking for values in different rows of a column. Sorry if it was not properly worded. So I used "vertical" because looking for values in different columns of a row with binary search is not supported, implemented atm.

>>I mean, are "horizontal" corner cases also covered?
Yes, they covered and give back the correct values, but we are using in that case the linear algorithm to search a value, even if we using the xlookup with binary search mode.
Comment 44 Commit Notification 2024-02-09 12:24:54 UTC
Balazs Varga committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/41192a36796155f8cd6ac733f5ef84767edf300f

Related: tdf#127293 Fix function XLOOKUP with nested XLOOKUP functions

It will be available in 24.8.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 45 ady 2024-02-12 15:36:30 UTC
tdf#159467 still fails as of 2024-02-12.
Comment 46 Balázs Varga (allotropia) 2024-02-12 18:01:34 UTC
(In reply to ady from comment #45)
> tdf#159467 still fails as of 2024-02-12.

Yeah, thats a specific case for XLOOKUP. All the other functions handle this situation differently. Will take a look that later. Otherwise this one can be closed I think.
Comment 47 Commit Notification 2024-02-22 20:58:58 UTC
Balazs Varga committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/d9635e9bb42cf2b744b8d2f74633e2a209a8e3fd

Related: tdf#127293 Fix typo for function XLOOKUP

It will be available in 24.8.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 48 Óvári 2024-02-25 10:20:00 UTC
(In reply to Winfried Donkers from comment #40)
> For 'Winfried Donkers' read 'Winfried Donkers and Balázs Varga'. Any
> compliments should go to Balázs.
Added to LibreOffice 24.8 Release Notes at https://wiki.documentfoundation.org/ReleaseNotes/24.8#Calc

Please update as required.

Hopefully it will enable more people to be aware of the function and can start testing and giving feedback.

Thank you
Comment 49 Óvári 2024-02-25 10:27:05 UTC
When should the "Feature Comparison: LibreOffice - Microsoft Office" page at https://wiki.documentfoundation.org/Feature_Comparison:_LibreOffice_-_Microsoft_Office#Desktop_Spreadsheet_applications:_LibreOffice_Calc_vs._Microsoft_Excel be updated?

Thank you
Comment 50 ady 2024-02-25 12:11:17 UTC
@Óvári,

If you actually read prior comments, feedback has been provided, and the function is not ready for usage. It would make no sense to promote testing (e.g. by adding it to the RN) when we already know it is not ready.
Comment 51 Balázs Varga (allotropia) 2024-02-25 15:21:25 UTC
(In reply to Óvári from comment #48)
> (In reply to Winfried Donkers from comment #40)
> > For 'Winfried Donkers' read 'Winfried Donkers and Balázs Varga'. Any
> > compliments should go to Balázs.
> Added to LibreOffice 24.8 Release Notes at
> https://wiki.documentfoundation.org/ReleaseNotes/24.8#Calc
> 
> Please update as required.
> 
> Hopefully it will enable more people to be aware of the function and can
> start testing and giving feedback.
> 
> Thank you

I will update next week as required. :) Thanks for the notifying. :)
Comment 52 Balázs Varga (allotropia) 2024-02-25 15:27:22 UTC
(In reply to ady from comment #50)
> @Óvári,
> 
> If you actually read prior comments, feedback has been provided, and the
> function is not ready for usage. It would make no sense to promote testing
> (e.g. by adding it to the RN) when we already know it is not ready.

I would say it's ready for usage (and testing), so can be added to release notes. The only remaining bit is this 159467 unique corner case which is only related to XLOOKUP when the inner XLOOKUP gives back an error... See the comments there.:https://bugs.documentfoundation.org/show_bug.cgi?id=159467#c6
Comment 53 ady 2024-02-25 19:40:16 UTC
(In reply to Balázs Varga (allotropia) from comment #52)
> I would say it's ready for usage (and testing), so can be added to release
> notes. The only remaining bit is this 159467 unique corner case which is
> only related to XLOOKUP when the inner XLOOKUP gives back an error... See
> the comments
> there.:https://bugs.documentfoundation.org/show_bug.cgi?id=159467#c6

@Balázs, I have to disagree. It is not a corner case. The fact that an inner XLOOKUP() triggers the case does not mean it is the only case, especially regarding lookup functions. Any argument that could trigger a different/ unexpected result will cause an unexpected failure, with users being unaware of this.

Any spreadsheet function has its own way of responding to certain arguments (empty/blank, or numeric vs text, sorted vs unsorted, and so on), and this is very important in any lookup function.

The function is ready for _alpha_ testing, where testers know they are supposed to search for problems, and not to blindly trust the results. Promoting it in release notes would hint for users – who very rarely read tickets – that it is ready for broader usage, and it is clearly not the case.

As for the release notes page, we have *several months* for 24.8. RN should not include items that are not ready for final users (which is not the same as alpha/beta testers). In the meantime, @Óvári already posted items regarding these functions, and I have set those as hidden for now.

Users already have several functions failing in Calc since LO 7.4, and most of them are unaware of it; let's not deceive common users nor treat them as alpha/beta testers, especially without them being aware of it.

Let's first see that at least the known bugs are actually resolved before promoting these functions more broadly.
Comment 54 Balázs Varga (allotropia) 2024-02-25 20:43:14 UTC
(In reply to ady from comment #53)
> (In reply to Balázs Varga (allotropia) from comment #52)
> > I would say it's ready for usage (and testing), so can be added to release
> > notes. The only remaining bit is this 159467 unique corner case which is
> > only related to XLOOKUP when the inner XLOOKUP gives back an error... See
> > the comments
> > there.:https://bugs.documentfoundation.org/show_bug.cgi?id=159467#c6
> 
> @Balázs, I have to disagree. It is not a corner case. The fact that an inner
> XLOOKUP() triggers the case does not mean it is the only case, especially
> regarding lookup functions. Any argument that could trigger a different/
> unexpected result will cause an unexpected failure, with users being unaware
> of this.

No, I meant that no any other functions working like that. All the other functions if they would have there same inner function, which would give back an error (#N/A etc.) would stop the evaluation, except the xlookup in excel. But will take a look that sooner or later.

> Let's first see that at least the known bugs are actually resolved before
> promoting these functions more broadly.

Agree with that. :)
Comment 55 Óvári 2024-02-25 22:10:23 UTC
Sorry for the misunderstanding.

It was understood that if an issue status is marked FIXED, then it is ready for adding to the Release Notes and promoting more generally.

Should the status be changed to ASSIGNED until all the know issues are resolved?

Thank you
Comment 56 Balázs Varga (allotropia) 2024-02-26 08:51:44 UTC
(In reply to Óvári from comment #55)
> Sorry for the misunderstanding.
> 
> It was understood that if an issue status is marked FIXED, then it is ready
> for adding to the Release Notes and promoting more generally.
> 
> Should the status be changed to ASSIGNED until all the know issues are
> resolved?
> 
> Thank you

No need, since we have a separate bug report for that remaining issue. :) (159467)