⚗️ Lox365: XLOOKUP for LibreOffice

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

Screenshot

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

Install or update

Download the extension Lox365.oxt from Releases.
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.

Screenshot of FILTER function

=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.

Screenshot of IMAGE function

=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.

Screenshot of SORT function

=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.

Screenshot of TEXTSPLIT function

=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.

Screenshot of TOCOL function

=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.

Screenshot of UNIQUE function

=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.

Screenshot of XLOOKUP function

=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

goosepirate / lox365

readme

⚗️ Lox365: XLOOKUP for LibreOffice

Install or update

Usage

Multiple outputs

Syntax

FILTER

IMAGE

SORT

TEXTSPLIT

TOCOL

UNIQUE

XLOOKUP

Why

Compatibility

Availability of other functions

References

Usage

Media

Development

LibreOffice API

Other