Bug 127293 - Add XLOOKUP function in Calc
Summary: Add XLOOKUP function in Calc
Status: ASSIGNED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium enhancement
Assignee: Winfried Donkers
URL:
Whiteboard:
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: 2022-10-29 08:45 UTC (History)
28 users (show)

See Also:
Crash report or crash signature:
Regression By:


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.