goosepirate/lox365

⚗️ Lox365: XLOOKUP for LibreOffice

⚗️ Lox365: XLOOKUP for LibreOffice

Lox365 is a LibreOffice Calc extension that adds modern spreadsheet functions like XLOOKUP, FILTER, and more.

Do you like using Lox365? Let me know in the Discussions. Maybe buy me a coffee.

Install or update

1. Download the extension Lox365.oxt from Releases.
2. Start LibreOffice > Tools > Extension Manager > Add > Select the oxt file > restart LibreOffice.

Usage

Use the function like you would any other function in LibreOffice Calc.

Multiple outputs

To output results in multiple cells, enter your formula with Ctrl + Shift + Enter.

This is because Lox365 functions are array functions. LibreOffice Calc does not support Excel's dynamic arrays.

Syntax

FILTER

Filters an array.

Similar to Excel's FILTER.

=FILTER(array, include, [if_empty])

• array: The array to filter.
• include: An array of booleans where TRUE represents a row or column to retain.
• [if_empty]: Returned if no items are retained. Optional.

IMAGE

Returns an image from a given source.

The source can be a local image file or a web URL. You can use a web URL of a third-party service to generate, for example, a QR code, equation, or diagram.

Similar to Excel's IMAGE.

=IMAGE(out_cell, source)

• out_cell: Reference to the cell where the image is to be placed.
• source: The path of the source that points to the image.

SORT

Sorts an array.

Similar to Excel's SORT.

=SORT(array, [sort_index], [sort_order])

• array: The array to sort.
• [sort_index]: A number indicating the row or column to sort by. Optional.
• [sort_order]: A number indicating the desired sort order; 1 for ascending order (default), -1 for descending order. Optional.
• Not supported: by_col.

TEXTSPLIT

Splits text into columns using delimiters.

Similar to Excel's TEXTSPLIT.

=TEXTSPLIT(text, col_delimiter)

• text: The text you want to split.
• col_delimiter: The text that marks the point where to split the text across columns.
• Not supported: row_delimiter, ignore_empty, match_mode, pad_with.

TOCOL

Returns the array as one column.

Similar to Excel's TOCOL.

=TOCOL(array)

• array: The array to return as a column.
• Not supported: ignore, scan_by_column.

UNIQUE

Returns the unique values from a range or array.

Similar to Excel's UNIQUE.

=UNIQUE(array)

• array: The array from which to return unique rows.
• Not supported: by_col, exactly_once.

XLOOKUP

Searches an array for a match and returns the corresponding item from a second array.

Similar to Excel's XLOOKUP.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

• lookup_value: The value to search for.
• lookup_array: The array to search.
• return_array: The array to return.
• [if_not_found]: Where a valid match is not found, return the [if_not_found] text you supply. Optional.
• Not supported: match_mode, search_mode.

Why

I use these functions quite often in Excel and wanted to use them in LibreOffice too, so I made this.

Here are what others are saying about this project:

Thanks for this; great idea!

— u/timespreader

Great work, goose! 😊

— Mike Saunders

Really nice idea.

— Behzat Yildirim

Very well done to the creator of the extension.

— Jimmy

Oh, wonderful!

😀 Thanks for implementing this!

— Arne

The support of XLOOKUP is a great addition.

— Marius Spix

Dobre rozszerzenie, bardzo przydatne funkcje.

— Piotr Osada

Pues muchísimas gracias.

— Guille

Bravo!

— Shakir Mahmud Sumon

The IMAGE function is very useful for those of us that import Google Docs that contain remote (URL base) images.

— René Haché

Keep up the great work!👍

— LeighAnne Kenney

👍😀

A 1000 thanks.

— Mikael Arling

The idea is splendid because Microsoft's IMAGE function in Excel doesn't work with local pictures or with pictures from LAN shares.

— Gabriela Salvisberg

Compatibility

Lox365 is not compatible with Microsoft Excel.

Lox365 must be installed in order to read and write Lox365 functions. You can check whether it is installed using LibreOffice Extension Manager.

On a machine without Lox365 installed, you will not be able to view calculation results from Lox365 functions.

Availability of other functions

These functions are not in LibreOffice and not provided by Lox365 but are available in the latest Excel:

• RANDARRAY
• SEQUENCE
• SORTBY
• STOCKHISTORY
• TOROW
• XMATCH

These functions are not in LibreOffice Calc now, but are planned to be added:

• XLOOKUP

These functions are already available in LibreOffice:

• CONCAT
• IFS
• MAXIFS
• MINIFS
• SWITCH
• TEXTJOIN

References

Usage

https://wiki.documentfoundation.org/Documentation/HowTo/install_extension

https://wiki.documentfoundation.org/Feature_Comparison:_LibreOffice_-_Microsoft_Office

Media

https://blog.documentfoundation.org/blog/2022/09/23/lox365-extension-xlookup-and-more-for-libreoffice-calc/

https://blog.documentfoundation.org/blog/2022/10/06/libreoffice-project-and-community-recap-september-2022/

https://www.reddit.com/r/libreoffice/comments/x98nqt/lox365_xlookup_for_libreoffice/

https://www.reddit.com/r/libreoffice/comments/xltuio/lox365_extension_xlookup_filter_sort_and_more_for/

https://www.reddit.com/r/opensource/comments/xfdmml/lox365_xlookup_for_libreoffice/

https://twitter.com/LibreOffice/status/1573232603351879682

https://fosstodon.org/@libreoffice/109046849962893237

https://www.facebook.com/libreoffice.org/posts/pfbid07mXEodbV2i32W6JkbRYWdDoyw8sUkiw7cX8QdTLU357AhJKGr9QoH5zKeJUxArkzl

[es] https://es.blog.documentfoundation.org/extension-lox365-xlookup-y-mas-para-libreoffice-calc/

[es] https://www.youtube.com/watch?v=BSPCJnc6r2g

[ja] https://forest.watch.impress.co.jp/docs/news/1442776.html

[ja] https://opensource.srad.jp/story/22/09/27/1337200/

[ja] https://www.zaikei.co.jp/article/20220928/691186.html

[pt] https://www.matsuura.com.br/2022/09/extensao-lox365-xlookup-e-mais-para.html

[tr] https://blog.libreoffice.org.tr/2022/09/23/libreoffice-calc-icin-yeni-bir-eklenti-goosepirate/

https://www.ubuntubuzz.com/2023/06/how-to-add-xlookup-function-to-libreoffice-calc.html

https://www.reddit.com/r/libreoffice/comments/16gaeth/lox365_xlookup_and_now_image_for_libreoffice/

https://fosstodon.org/@libreoffice/111052323463423962

https://twitter.com/LibreOffice/status/1701582897784729628

https://www.facebook.com/libreoffice.org/posts/pfbid02882qs7Ek5EZXgSkpkZFMHXCLHszP87pUT8uKcHDpSfwAZfWVytHanGh5fFGocieFl

[zh] https://www.youtube.com/watch?v=ndvbu0kC83o

https://www.youtube.com/watch?v=_zHuJIFLSMw

Development

https://bugs.documentfoundation.org/show_bug.cgi?id=126573

https://bugs.documentfoundation.org/show_bug.cgi?id=127293

https://gerrit.libreoffice.org/c/core/+/131905

https://flywire.github.io/lo-p/

https://wiki.openoffice.org/wiki/Calc/Add-In/Python_How-To

https://wiki.openoffice.org/wiki/Python/Python_Language_Binding

https://wiki.documentfoundation.org/Documentation/DevGuide/Spreadsheet_Documents

https://help.libreoffice.org/latest/en-US/text/sbasic/python/main0000.html

https://help.libreoffice.org/latest/en-US/text/sbasic/python/python_programming.html

https://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/module-ix.html

https://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/AddIn.html

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

https://forum.openoffice.org/en/forum/

LibreOffice API

https://api.libreoffice.org/docs/idl/ref/namespaces.html

https://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1beans_1_1XPropertySet.html

https://api.libreoffice.org/docs/idl/ref/servicecom_1_1sun_1_1star_1_1sheet_1_1AddIn.html

https://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1table_1_1XCellRange.html

https://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1uno_1_1XInterface.html

Other

https://extensions.libreoffice.org/en/extensions/show/27434

https://bugs.documentfoundation.org/

https://ask.libreoffice.org/

https://forum.openoffice.org/en/forum/index.php

lox365's Issues

How filter and sort functions work?

Hi! Please, explain, how these functions work? Eg, I am trying to select the range, use as the argument, but the data are not populated automatically, only the first upper cell. Thanks.

Cannot install on libreoffice

Currently trying to install the extension on LibreOffice 7.3.7.2 30(Build:2) yield the following error on installation.
I tried restarting the app but the functionalities are not added.

Could you help fix this issue plase?

XLOOKUP Return Value doesn't match IF test

I'm using XLOOKUP to return a string from the return array, when it matches the lookup array.
The function successfully returns the string.
However, the string does not match an IF test in another cell

e.g.
=XLOOKUP("TEST",B2:B4,C2:C4,"ERR") TEST ReturnString
=ReturnString

However, an IF function will not match "ReturnString"

function SORT issue

Hi, I'v downloaded en installed the extension in LO 7.6.0.3. Function SORT is available, seems to work when selected an array, But when entered, it displays only one cel (the first one), Something is wrong with [sort index] in my case. It doesn't accept any value for colomn/row. Any idea what is wrong or what I'm doing wrong?
Thanks for the feature anyway!

Edit when used UNIQUE i got an similar issue. Only one value is displayed.
kind regards

filter with dynamic filter value returns unexpected results

I'm trying to use a value in a cell D1 as filtering value.

The image above shows the result for value 4. the next ones show the result for 3, 2 and 1.

What is the correct approach to filter a list by a value that might change?

BTW - if I expand the list A2:B4 to something bigger the FILTERed list doesn't expand - I need to delete and create it from scratch.

Found a way to at least partially add search mode

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>

SORT() function, sort numbers as if they were string, (1,11,2)

SORT() function, sort numbers as if they were string,
1
2
11
returns
1
11
2

Will be integrated into Calc in future?

As title says---

Returning multiple columns/rows not working

Hello,
I try to use the function to output multiple rows. As a return array I entered a two-dimensional array (e.g. B3:C9), but as result I just get one value from the one dimensional array in column B.

Release notes

Hello.

Please write release notes for each release, for example version 5.0

Thank you.

install err

Ver.4
libreoffice 7.3 and 7.4
install this ext.cannot install . this daialog messege.

(com.sun.star.uno.RuntimeException) { { Message = "<class 'TypeError'>: 'type' object is not subscriptable, traceback follows\X000a File "C:\Program Files\LibreOffice\program\pythonloader.py", line 147, in writeRegistryInfo\X000a mod = self.getModuleFromUrl( locationUrl )\X000a File "C:\Program Files\LibreOffice\program\pythonloader.py", line 102, in getModuleFromUrl\X000a exec(codeobject, mod.dict)\X000a File "C:\Users\baian\AppData\Roaming\LibreOffice\4\user\uno_packages\cache\uno_packages\lu281968eed7m.tmp_\Lox365 (2).oxt\loader.py", line 6, in \X000a class Lox365(unohelper.Base, XLox365):\X000a File "C:\Users\baian\AppData\Roaming\LibreOffice\4\user\uno_packages\cache\uno_packages\lu281968eed7m.tmp_\Lox365 (2).oxt\loader.py", line 9, in Lox365\X000a def _get_dataarray(self, cellrange, positions: dict) -> tuple[tuple]:\X000a\X000a", Context = (com.sun.star.uno.XInterface) @0 } }

Filter not working

Thanks Goose for your work on this extension! I've been trying to use this but look like the FILTER function doesn't work. Are you able to provide an example file that's working, or an example formula?

I've been troubleshooting this for the last 3 hours...lol. Looked on google and try many different variations of the Excel FILTER function but always returning no results.

FILTER, dont work for me

What im doing wrong?
This is the sheet

There is the formula

=FILTER(A4:G32,A4:A10=I3,"")
What can i do?
Thanks.

Reverse text

Hi Goosepirate

Would you consider adding another LibreOffice Calc function, ReverseText, which reverses a text string? So ABC becomes CBA.

Use case for this is explained in this thread: https://twitter.com/Tagishsimon/status/1371384451754778627 ... should say that twitter does not do a good job of displaying the whole thread; there are about 10+ tweets explaining the need; clicking on the bottom tweet will force twitter to display the next tweet.

thanks
simon

XLOOKUP("E", A:A, B:B) is very slow as compared to XLOOKUP("E", A2:A4, B2:B4)

XLOOKUP("E", A:A, B:B) is very slow as compared to XLOOKUP("E", A2:A4, B2:B4).

I think the code is iterating through each row until the MAX possible row, rather than identify the last used row, for a match.

Nested XLOOKUP doesn't appear to work

Using a 2nd XLOOKUP as the [if not found] argument does not appear to work.

XLOOKUP( value, lookup1, return1, XLOOKUP( value, lookup2, return2, "not found" ) )

a bug? #NAME? when "Use English function names" is enabled

Hi.

I've found a bug.

I've installed this wonderful extension today, but when I enable "Use English function names" in Calc settings (which I really need) every lox365 function stops working and gives out #NAME? error.

Please, advise.

Edit. Found a bypass but suboptimal:
added COM.GOOSEPIRATE.LOX365.OXT. before every function name ie. COM.GOOSEPIRATE.LOX365.OXT.XLOOKUP and all is well even if "Use English function names" is activated.
it is suboptimal, though.