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.