Found a way to at least partially add search mode

[ghost]

Hello, The reason I want to use XLOOKUP is to perform a reverse search starting at the last item, so I modified your code to add that part. I've only added it in the vertical direction but hopefully this will help you make a version that works for vertical too. I also didn't add the binary search modes. Here is the modified code for the lox365.py file:

def XLOOKUP(lookupValue, lookupArray, returnArray, ifNotFound=ERR_NA, searchMode=1):
    if ifNotFound is None: ifNotFound = ERR_NA
    if searchMode is None: searchMode = 1
    lookup_direction = 0 # 0 is vertical; 1 is horizontal
    if len(lookupArray) == 1 and len(lookupArray[0]) > 1: lookup_direction = 1
    try:
        if searchMode == -1:
            if lookup_direction == 0:
                return (returnArray[len(returnArray) - lookupArray[::-1].index((lookupValue,)) - 1],)
            if lookup_direction == 1:
                return tuple((row[lookupArray[0].index(lookupValue)],) for row in returnArray)
        else:
            if lookup_direction == 0:
                return (returnArray[lookupArray.index((lookupValue,))],)
            if lookup_direction == 1:
                return tuple((row[lookupArray[0].index(lookupValue)],) for row in returnArray)
    except ValueError: return ((ifNotFound,),)

Here is the added node to the addin.xcu file:

            <node oor:name="searchMode" oor:op="replace">
              <prop oor:name="DisplayName"><value xml:lang="en">[Search mode]</value></prop>
              <prop oor:name="Description"><value xml:lang="en">The order of the search. 1=Start from first item (default) -1=Reverse.</value></prop>
            </node>

[goosepirate]

Hi, nice work! Would you be willing to add the reverse search in the horizontal direction? If so I will add it to the code.

[ovari]

XLOOKUP has been added to LibreOffice 24.8 as shown at https://wiki.documentfoundation.org/ReleaseNotes/24.8#Calc

Can you please test that the reverse search works for you in both the vertical and horizontal directions using LibreOffice 24.8?

Thank you

[Nekkowe]

as shown at https://wiki.documentfoundation.org/ReleaseNotes/24.8#Calc

It doesn't say that there, though?

[ovari]

@Nekkowe it was shown in the Release notes; however, it seems that has been removed and will be re-added later.

The XLOOKUP function has been implemented as shown in https://bugs.documentfoundation.org/show_bug.cgi?id=127293#c39

You can test in the daily builds. Links to the daily builds are shown in the LibreOffice issue tracker.

For your information, XMATCH and FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, RANDARRAY functions are being added too.

[Nekkowe]

Ooooh, nice!! I've really been looking forward to some of these..! Thank you for the information!

[ovari]

Ooooh, nice!! I've really been looking forward to some of these..! Thank you for the information!

Can you please merge https://github.com/goosepirate/lox365/pull/20? Hopefully this can help discover, test and provide feedback to lox365 users?

Thank you

[Nekkowe]

Me? I'm afraid I'm not a maintainer, just a passerby, so I don't have write access to this repository... you'll want @goosepirate for that, I think.

[ovari]

@Nekkowe FYI below is the LibreOffice Help XLOOKUP Function link https://help.libreoffice.org/master/en-US/text/scalc/01/func_xlookup.html

[ovari]

Hi, nice work! Would you be willing to add the reverse search in the horizontal direction? If so I will add it to the code.

LibreOffice 24.8 implements the XLOOKUP function https://bugs.documentfoundation.org/show_bug.cgi?id=127293

LibreOffice Help also includes the XLOOKUP function https://help.libreoffice.org/master/en-US/text/scalc/01/func_xlookup.html

@goosepirate does LibreOffice 24.8 implementation of the XLOOKUP function add the reverse search in the vertical and horizontal directions?

Thank you

[ovari]

as shown at https://wiki.documentfoundation.org/ReleaseNotes/24.8#Calc

It doesn't say that there, though?

It's there now, please see screenshot below.

[ovari]

OASIS OFFICE-4154 add XLOOKUP function includes search mode which has been implemented in Bug 127293 - Add XLOOKUP function in Calc and available in LibreOffice Calc 24.8.

@goosepirate can this issue please be closed?

Thank you