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: https://issues.oasis-open.org/browse/...
Whiteboard: target:24.8.0 inReleaseNotes:24.8 tar...
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-07-01 13:23 UTC (History)
37 users (show)

See Also:
Crash report or crash signature:


Attachments
XLOOKUP() with regular expressions vs wildcards (9.88 KB, application/vnd.oasis.opendocument.spreadsheet)
2024-04-11 14:48 UTC, ady
Details
Sheet to illustrate the influence of the 'case-sensitive' option on XLOOKUP and FILTER (59.46 KB, application/vnd.oasis.opendocument.spreadsheet)
2024-05-20 14:59 UTC, Jürgen Kirsten
Details

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)
Comment 57 David Lynch 2024-04-10 08:30:36 UTC
The help for this function <https://help.libreoffice.org/master/en-US/text/scalc/01/func_xlookup.html> states "XLOOKUP function is a modern and flexible replacement for older functions like VLOOKUP, HLOOKUP, and LOOKUP."
However, it states merely that it supports "wildcards (* ?) for partial matches", whereas the older functions support (the full set of) regular expressions.
Please can XLOOKUP (and similar new functions) support the full set of regular expressions?

David Lynch
(also posted to Libreoffice users group)
Comment 58 Winfried Donkers 2024-04-11 05:25:31 UTC Comment hidden (obsolete)
Comment 59 Mike Kaganski 2024-04-11 05:48:45 UTC Comment hidden (obsolete)
Comment 60 David Lynch 2024-04-11 06:33:21 UTC Comment hidden (obsolete)
Comment 61 ady 2024-04-11 06:58:41 UTC Comment hidden (obsolete)
Comment 62 Balázs Varga (allotropia) 2024-04-11 09:52:17 UTC
(In reply to ady from comment #61)
> (In reply to Winfried Donkers from comment #58)
> > If you change the (advanced) settings of LibreOffice to use regular
> > expressions in Calc (search for RegularExpression), XLOOKUP will work with
> > regular expressions.
> 
> @Winfried (and/or whoever tested Regular Expressions with XLOOKUP)...
> 
> Are you stating the above quote as a general rule? Or, instead, have you
> actually tested using regex on any argument of XLOOKUP()?
> 
> If anyone actually tested regex in XLOOKUP(), please share your results.
> 
> I am asking because I have actually tested using regex in the Search
> Criteria argument of XLOOKUP() and it did not work for me – I compared the
> result with the equivalent VLOOKUP(), which indeed supports regex.
> 
> FWIW, I tried with different values on the "Match Mode" argument of
> XLOOKUP() (including using 2, which is needed for wildcards); all failed to
> work when the "Search Criteria" argument was using regex (while the relevant
> Option in Calc was set for regex, not for wildcards).
> 
> IOW, my tests correspond to the current Help content.

Thanks for testing it Ady. :) Can you share some documents with the example functions included with the worked VLOOKUP and with the not working XLOOKUP? Would be useful for checking and fixing it.
Comment 63 ady 2024-04-11 14:27:15 UTC Comment hidden (obsolete)
Comment 64 ady 2024-04-11 14:38:07 UTC Comment hidden (obsolete)
Comment 65 ady 2024-04-11 14:48:15 UTC
Created attachment 193623 [details]
XLOOKUP() with regular expressions vs wildcards

ODS for testing XLOOKUP() with regular expressions vs wildcards
Comment 66 Balázs Varga (allotropia) 2024-04-11 15:42:54 UTC
(In reply to ady from comment #65)
> Created attachment 193623 [details]
> XLOOKUP() with regular expressions vs wildcards
> 
> ODS for testing XLOOKUP() with regular expressions vs wildcards

Thanks a lot. I think I know where will be the problem. Will check tmr.
Comment 67 Commit Notification 2024-04-12 08:23:13 UTC
Balazs Varga committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/51abf44808c6793a184e986b62c0786753e11ded

Related: tdf#127293 Fix regex search mode in XLOOKUP wildcards mode

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 68 ady 2024-04-12 17:52:12 UTC
(In reply to Commit Notification from comment #67)

> Related: tdf#127293 Fix regex search mode in XLOOKUP wildcards mode

@Balázs, please clarify what should the expected behavior be after that patch.

I assume that regex should work when the adequate Options is set to use regex, while using MatchType set to 2 (i.e. "Partial Match", not just "Wildcard Match") in XLOOKUP().

What about other values of MatchType? I would assume that regex should not work with a MatchType argument different than 2, so the function would be 100% compatible with other spreadsheet tools in such cases. OTOH, I can also imagine some users asking for regex to work in those cases anyway (although I don't have a specific use-case in mind ATM).

So, at least for tests and documentation purposes, would you please clarify? TIA.
Comment 69 Balázs Varga (allotropia) 2024-04-12 18:52:20 UTC
(In reply to ady from comment #68)
> (In reply to Commit Notification from comment #67)
> 
> > Related: tdf#127293 Fix regex search mode in XLOOKUP wildcards mode
> 
> @Balázs, please clarify what should the expected behavior be after that
> patch.

Yes, sorry about that.

> 
> I assume that regex should work when the adequate Options is set to use
> regex, while using MatchType set to 2 (i.e. "Partial Match", not just
> "Wildcard Match") in XLOOKUP().

Yes exactly. If the Regex mode is selected and the MatchType set to 2 then it should work with Regex, if the Wildcard mode is selected and the MatchType set to 2 then it should work with Wildcards. Just some more info from MS page: https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929

> 
> What about other values of MatchType? I would assume that regex should not
> work with a MatchType argument different than 2, so the function would be
> 100% compatible with other spreadsheet tools in such cases.

Yes, exactly, it should only works with MatchType 2.
Comment 70 Commit Notification 2024-05-07 11:14:47 UTC
Balazs Varga committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/0ca20dca3349daa303b89251443f550491968a39

Related: tdf#127293 Add unit test for xlookup regex search mode

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 71 Óvári 2024-05-16 07:52:19 UTC
In release notes:
https://wiki.documentfoundation.org/ReleaseNotes/24.8#Calc
Comment 73 Jürgen Kirsten 2024-05-20 14:38:16 UTC
@ balazs.varga991@gmail.com
Thank you very much for your tireless efforts to implement XLOOKUP() and XMATCH().

I have tested the functions and encountered a problem.

My test environment:
Version: 24.8.0.0.alpha1+ (X86_64) / LibreOffice Community
Build ID: 1b45ca1aa7d7cb8e7adcc07f8c60e26a413eca8c
CPU threads: 8; OS: Windows 11 X86_64 (10.0 build 22631); UI render: Skia/Vulkan; VCL: win
Locale: en-GB (de_DE); UI: en-GB
Calc: CL threaded

XLOOKUP() but also other (XVHL)OOKUP() functions do not differentiate between upper and lower case when searching for letters. This is also the case in other spreadsheets. Microsoft recommends in its documentation to use the EXACT() function if you want to differentiate.
Since CALC has the 'Case-sensitive' option, which sets the behaviour to be case-sensitive when interacting with EXCEL, I assumed that CALC does it differently and is case-sensitive.

Was it considered during development to introduce the differentiation for CALC and only behave exactly like Excel when 'case-sensitive' is switched off?

The change in the 'Case-sensitive' option is most noticeable in the direct comparison A1=B1. If the option is switched on, "Aa"<>"aa", if it is switched off you get TRUE.
This also means that the formula =XLOOKUP(1,A2:A5="AA",B2:B5) does not give the same result as =XLOOKUP("AA",A2:A5,B2:B5) when the option is switched on and A2="aa" & A3="AA". 

I was about to ignore all this and dismiss it as not so important when I realised that the FILTER() function uses exactly this distinction between upper and lower case when the 'Case-sensitive' option is switched on.
In Microsoft Excel, FILTER() is not case-sensitive. As far as I know, you cannot switch it on or off.

Kind regards

Jürgen
Comment 74 Jürgen Kirsten 2024-05-20 14:59:59 UTC
Created attachment 194230 [details]
Sheet to illustrate the influence of the 'case-sensitive' option on XLOOKUP and FILTER

See my Comment No 73
Comment 75 Commit Notification 2024-06-04 10:10:34 UTC
Balazs Varga committed a patch related to this issue.
It has been pushed to "master":

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

Related: tdf#128425 tdf#127293 - Extend function wizard description

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 76 ady 2024-06-04 12:39:30 UTC
Just in case and for the record, MS Excel will add to XLOOKUP and to XMATCH a new match mode for regex, so this is something that might be worth considering for compatibility in the future.

Currently in Calc we have either wildcards or regex, depending on Calc's settings. In a future development, we might also have/need to support regex depending on the function's arguments (and independent on Calc's settings for that argument's value).

<https://insider.microsoft365.com/en-us/blog/new-regular-expression-regex-functions-in-excel#:~:text=Regex%20coming%20soon%20to%20XLOOKUP,as%20the%20'lookup%20value'.>
Comment 77 Óvári 2024-06-09 04:18:27 UTC
ODF proposal is here:
https://issues.oasis-open.org/browse/OFFICE-4154
Comment 78 Commit Notification 2024-06-21 08:05:51 UTC
Balazs Varga committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/17d578ba91f9c78a0e41d19b58183d2214c0b7a4

Related: tdf#127293 Add new Match_mode option for XLOOKUP and

It will be available in 25.2.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 79 ady 2024-06-21 12:53:29 UTC
(In reply to Commit Notification from comment #78)
> https://git.libreoffice.org/core/commit/
> 17d578ba91f9c78a0e41d19b58183d2214c0b7a4
> 
> Related: tdf#127293 Add new Match_mode option for XLOOKUP and

@Balázs,

Will Match_mode "2" still support regex when the "Enable regular expressions in formulas" setting is enabled?
Comment 80 Balázs Varga (allotropia) 2024-06-21 15:32:04 UTC
(In reply to ady from comment #79)
> Will Match_mode "2" still support regex when the "Enable regular expressions
> in formulas" setting is enabled?

No, it will be indipendent from these global properties, as it was discussed:
(So Match_mode "2" only support Wildcard, Match_mode "3" only support Regex)

"The ODF TC will follow that in the specification of these functions and
at the same time specify, that the host dependent properties HOST-USE-REGULAR-EXPRESSIONS
and HOST-USE-WILDCARDS (file format table:use-regular-expressions and table:use-wildcards)
will be ignored by these functions."
Comment 81 ady 2024-06-21 16:10:51 UTC Comment hidden (obsolete)
Comment 82 Commit Notification 2024-06-24 09:39:36 UTC
Balazs Varga committed a patch related to this issue.
It has been pushed to "libreoffice-24-8":

https://git.libreoffice.org/core/commit/607b99ea5b1b1e46622262cc5cfbeea01178d751

Related: tdf#127293 Add new Match_mode option for XLOOKUP and

It will be available in 24.8.0.0.beta2.

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 83 Commit Notification 2024-06-27 16:06:53 UTC
Balazs Varga committed a patch related to this issue.
It has been pushed to "master":

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

Related: tdf#127293 Ignore 'search-criteria-must-apply-to-whole-cell'

It will be available in 25.2.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 84 Commit Notification 2024-07-01 13:23:44 UTC
Balazs Varga committed a patch related to this issue.
It has been pushed to "libreoffice-24-8":

https://git.libreoffice.org/core/commit/5127f1678f0fc0c1c9096a76691f9b5220b16631

Related: tdf#127293 Ignore 'search-criteria-must-apply-to-whole-cell'

It will be available in 24.8.0.0.beta2.

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.