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: -
See also accompanying XMATCH: https://support.office.com/en-us/article/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312
*** Bug 130359 has been marked as a duplicate of this bug. ***
Explained function in Spanish: https://ayudaexcel.com/por-que-la-funcion-buscarx-esta-revolucionando-las-busquedas/
XLOOKUP is a great enhancement over VLOOKUP. Would love to see it in Calc. Another detailed description: https://www.excel-university.com/xlookup/
+1
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.
Since this is an enhancement, please keep version as unspecified.
Would be nice to see XLOOKUP quickly in calc. We got Excel files from partners with this function and is it not usable on our side. 7.3? 7.4? Thanks ;)
We are also looking to make the transition to LibreOffice, but can't do so because some documents we work with use the XLOOKUP feature. Other office suites have already added XLOOKUP to their functions list, so it would be great to see it in LibreOffice.
I'll have a go at it.
Hi, what about this feature in the upcoming LO 7.4?
No. There will be commit notifications and a whiteboard target once it got implemented.
Google Sheets, OnlyOffice and Excel now have the function XLOOKUP. It is important for import documents.
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.
(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.
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.
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.
(In reply to gmolleda from comment #17) https://1drv.ms/x/s!AqRfhRdisQhQg7pMU14AUX8vt2nA-g?e=zVFenh
You could change the XLOOKUP to: XLOOKUP(3,A1:A9,B1:B9,,-1,-1) and the result will be the same that VLOOKUP.
(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.
(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).
(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.
(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)
(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)
(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.
(In reply to Mike Kaganski from comment #25) Oh sorry, that needs more development.
This is the gerrit link, where Winfried is working on the patch: https://gerrit.libreoffice.org/c/core/+/131905
(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.
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.
(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?
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.
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
(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.
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
(In reply to LBR from comment #34) > 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 Are there any other functions missing in LibreOffice Calc core functionality that require Microsoft Excel or Google Sheets in the meantime? Having a complete list of all the missing functions could help TDF to strategize more effectively. Thank you
(In reply to Óvári from comment #35) > Are there any other functions missing in LibreOffice Calc core functionality > that require Microsoft Excel or Google Sheets in the meantime? There are bug 126573 and Excel-Functions META bug 150900.
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?
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].
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.
(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.
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.
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.)
(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.
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.
tdf#159467 still fails as of 2024-02-12.
(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.
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.
(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
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
@Ó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.
(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. :)
(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
(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.
(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. :)
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
(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)
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)
(In reply to David Lynch from comment #57) If you change the (advanced) settings of LibreOffice to use regular expressions in Calc (search for RegularExpression), XLOOKUP will work with regular expressions.
(In reply to David Lynch from comment #57) > The help for this function ... states merely that it supports "wildcards (* ?) > for partial matches" This is a documentation issue. The help must have the same block as elsewhere, mentioning the settings that control the wildcard / regex behavior, and related stuff. So, that is something to be fixed in a follow-up to bug 159596.
Many thanks for your reply and the new functions. I anticipate them enabling much simpler and more maintainable spreadsheets.
(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.
(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.
(In reply to Balázs Varga (allotropia) from comment #62) > Can you share some documents with the example > functions included with the worked VLOOKUP and with the not working XLOOKUP? On further testing, when XLOOKUP() is _explicitly_ introduced as array formula, the regex expression is correctly used. But when the formula is not introduced as array, the regex fails. Not using CSE is a very common (user's) mistake, especially in cases when a formula would work either way, and even more common when we talk about a function in "dynamic-array-era"'s Excel, where users don't need to use CSE. @Balázs, do you still need a sample ods file? Or, are the above paragraphs enough? BTW, using the Function Wizard, the on-screen help text for the arguments "Match Mode" and "Search Mode" could be improved in order to avoid mistakes (instead of users having to remember the meaning of the alternative numeric values).
(In reply to ady from comment #63) > On further testing, when XLOOKUP() is _explicitly_ introduced as array > formula, the regex expression is correctly used. But when the formula is not > introduced as array, the regex fails. I spoke too soon. I might had looked into the wrong cell or something. It fails anyway, with or without CSE. I'll add a sample ods file.
Created attachment 193623 [details] XLOOKUP() with regular expressions vs wildcards ODS for testing XLOOKUP() with regular expressions vs wildcards
(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.
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.
(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.
(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.
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.
In release notes: https://wiki.documentfoundation.org/ReleaseNotes/24.8#Calc
Links for testers: 1. https://bettersolutions.com/excel/functions/xlookup-function.htm 2. https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929 3. https://support.google.com/docs/answer/12405947
@ 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
Created attachment 194230 [details] Sheet to illustrate the influence of the 'case-sensitive' option on XLOOKUP and FILTER See my Comment No 73
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.
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'.>
ODF proposal is here: https://issues.oasis-open.org/browse/OFFICE-4154
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.
(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?
(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."
@Balázs, thank you for the prompt reply. (In reply to Commit Notification from comment #78) > Related: tdf#127293 Add new Match_mode option for XLOOKUP and > > It will be available in 25.2.0. I guess this will be back-ported to the 24.8 branch in the near future.
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.
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.
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.