XLOOKUP

Searches for a value in an array and returns a reference to a cell or range of cells.

XLOOKUP function is a modern and flexible replacement for older functions like VLOOKUP, HLOOKUP, and LOOKUP. XLOOKUP supports approximate and exact matching, wildcards (* ?) or regular expressions for partial matches, and lookups in vertical or horizontal ranges. XLOOKUP can perform a reverse search and offers a fast binary search option when working with large datasets.

Syntax

XLOOKUP( [Search criterion] ; Search Array ; Result Array [ ; [ Result if not found ] [ ; [Match Mode] [ ; Search Mode ] ] ] )

Search criterion: (optional) The value of any type to search for in Array. If omitted, XLOOKUP returns blank cells it finds in Search Array.

Search Array: is the reference of the array to search. Array must be a 1-dimensional array and must be contained in one sheet only.

Result Array: is the reference of the array or range to return.

If Result Array is a range of cells, the XLOOKUP function must be entered as an array formula.

Result if not found: a text or cell content to return if the Lookup value is not found. If a valid match is not found and Result if not found is omitted, the function returns the #N/A error.

Match Mode: (optional) specifies the match type. Values can be:

0: exact match (default). If Search criterion value is not found and Result if not found text is omitted, then return the #N/A error.
-1: attempt exact match. If Search criterion value is not found, then return the next smaller item.
1: attempt exact match. If Search criterion value is not found, then return the next larger item.
2: a wildcard match where characters * (multiple characters), ? (single character), and ~ have special meanings.
3: a regular expression match.

Search Mode: (optional) specifies the search mode to use.

1: returns the first occurrence starting from the first item of Search Array (default).
-1: reverse search. Returns the first occurrence starting from the last item of Search Array.
2: binary search that relies on Search Array being sorted in ascending order. If not sorted, invalid results will be returned.
-2: binary search that relies on Search Array being sorted in descending order. If not sorted, invalid results will be returned.

Match Mode values 2 and 3 cannot be combined with binary search (Search Mode value 2 or -2).

Any of the optional arguments can be omitted. An optional argument requires all preceding separators to be present.

Examples

Suppose we have built a small database table occupying the cell range A1:DO4 and containing basic information about 118 chemical elements. The first column contains the row headings “Element”, “Symbol”, “Atomic Number”, and “Relative Atomic Mass”. Subsequent columns contain the relevant information for each of the elements, ordered left to right by atomic number. For example, cells B1:B4 contain “Hydrogen”, “H”, “1” and “1.008”, while cells DO1:DO4 contain “Oganesson”, “Og”, “118”, and “294”.

	A	B	C	D	...	DO
1	Element	Hydrogen	Helium	Lithium	...	Oganesson
2	Symbol	H	He	Li	...	Og
3	Atomic Number	1	2	3	...	118
4	Relative Atomic Mass	1.008	4.0026	6.94	...	294

{=XLOOKUP("Atomic Number";A2:A4;A2:DO4)} returns the array

Atomic Number

...

118

{=XLOOKUP("Helium";B1:DO1;B1:DO4)} returns the array

Helium

4.0026

{=XLOOKUP("Kryptonite";B1:DO1;B1:DO4;"Unknown element")} returns the array {"Unknown element","Unknown element","Unknown element","Unknown element"}.

Technical information

This function is available since LibreOfficeDev 24.8.

This function is not part of the Open Document Format for Office Applications (OpenDocument) Version 1.3. Part 4: Recalculated Formula (OpenFormula) Format standard. The name space is

COM.MICROSOFT.XLOOKUP

XMATCH

XLOOKUP wiki page.