Documentation/Calc Functions/XLOOKUP

Under construction.
For now, see online help for XLOOKUP.

Function name:

XLOOKUP

Category:

Spreadsheet

Summary:

Search for a value in an array and return a reference to a corresponding cell.

XLOOKUP is a modern and flexible replacement for older functions like VLOOKUP, HLOOKUP, and LOOKUP.

XLOOKUP supports several matching modes: approximate matching, exact matching, partial matches using either wildcards (* ? ~) or regular expressions, and lookups in either vertical or horizontal ranges. XLOOKUP can perform a reverse search, and offers a fast binary search option.

Syntax:

XLOOKUP([Lookup] ; Array; Return [ ; [NotFound] [ ; [MatchType] [ ; SearchMode ] ] ])

Returns:

If a match of value Lookup is found in Array, then XLOOKUP returns a reference to the cell in Return that has the same relative position as the matching item has in Array.

If no match is found, then the return value depends on the NotFound parameter.

Arguments:

Lookup (optional) is a value of any type to search-for in Array. If omitted, XLOOKUP searches for a blank cell in Array.

Array specifies the 1-dimensional array to search in. Array may be given as:

direct reference with range operator: C2:C8 or B4:K4
inline-array with constants: {"Apple"; "Peach"; "Orange"; "Banana"}
column of a database-range with labels: myData[Person] with database-range myData and database-field Person
named expression or label for a 1-dimensional array of cells
expression, that results in a 1-dimensional array or a reference to a 1-dimensional array of cells.

Warning:
Array and Return must have the same total number of elements.

Return specifies the 1-dimensional array of cells from which to build the return value. Return may be given as:

direct reference with range operator
column of a database-range with labels
named expression or label for a 1-dimensional array of cells
expression, that results in a reference to a 1-dimensional array of cells.

NotFound (optional) may be any expression. It is evaluated and the result returned, if a match for the Lookup value is not found. If a valid match is not found and NotFound is omitted, the function returns the #N/A error.

MatchType (optional) specifies the match type:

0 (default) Exact match.
-1 Approximate match. If an exact match was not found, search for the largest value that is smaller than Lookup value.
1 Approximate match. If an exact match was not found, search for the smallest value that is larger than Lookup value.
2 A wildcard match.
3 A regular expression match.

Warning:
MatchType values 2 and 3 shall not be used together with binary search (SearchMode values 2 and -2).

SearchMode (optional) specifies the search mode:

1 (default) Perform a linear search starting at the first item in Array.
-1 Perform a reverse linear search starting at the last item in Array.
2 Perform a binary search. The binary search relies on Array being sorted in ascending order. If not sorted, invalid results will be returned.
-2 Perform a binary search. The binary search relies on Array being sorted in descending order. If not sorted, invalid results will be returned.

Additional details:

The approximate match and the sorting requested for binary search use "alphanumeric" ordering rules.

By default, the lookup is not case-sensitive, overriding Tools ▸ Options ▸ Calc ▸ Calculate ▸ (General Calculations) Case sensitive. You can use mode modifiers in a regular expression to specify a case-sensitive search.

Note:
Match must apply to the entire cell content, overriding Tools ▸ Options ▸ Calc ▸ Calculate ▸ (General Calculations) Search criteria = and <> must apply to whole cells. To get a match with only part of the cell content, use either wildcard match or regular expression match with suitable Lookup values.

Note:
XLOOKUP ignores the setting of "Enable wildcards in formulas" and "Enable regular expressions in formulas" in Tools ▸ Options ▸ Calc ▸ Calculate ▸ (Formulas Wildcards). A match in MatchType values 0, 1 and -1 never uses wildcards nor regular expressions.

The implementation in LibreOffice allows to specify Array or Return by an expression like myData[Person], where myData is the name of a database range that contains labels, and Person is such a label. However, when saving to ODF format, this expression is replaced by the absolute address of the array at the time of saving, since ODF has no means to store the myData[Person] form. To make formulas better legible in ODF format, named expressions and labels can be used instead of this special form. When saving as an xlsx document, a database range is translated into an "Excel table" and the expression myData[Person] is retained. Excel interprets it the same way as LibreOffice, and reopening the xlsx file in LibreOffice still displays the expression myData[Person].

Examples:

Related LibreOffice functions:

ODF standard:

XLOOKUP is not yet standardized. Adding this function to the ODF standard is tracked in issue OFFICE-4154.

See instead COM.MICROSOFT.XLOOKUP

XLOOKUP is available in LibreOffice since version 24.8.

Related (or similar) Excel functions:

XLOOKUP since v.2021.