# Documentation/Calc Functions/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:
with database-range`myData[Person]`

and database-field`myData`

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

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

**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 .
You can use ▸ ▸ ▸ ▸ *mode* modifiers in a regular expression to specify a case-sensitive search.

The implementation in LibreOffice allows to specify **Array** or **Return** by an expression like * myData[Person]*, where

*is the name of a database range that contains labels, and*

`myData`

*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*

`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]`

*.*

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