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.

αƒ‘αƒ˜αƒœαƒ’αƒαƒ₯αƒ‘αƒ˜

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.

note

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:

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

warning

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


note

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


αƒ›αƒαƒ’αƒαƒšαƒ˜αƒ—αƒ”αƒ‘αƒ˜

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

αƒ”αƒšαƒ”αƒ›αƒ”αƒœαƒ’αƒ˜

αƒ¬αƒ§αƒαƒšαƒ‘αƒαƒ“αƒ˜

αƒ°αƒ”αƒšαƒ˜αƒ£αƒ›αƒ˜

αƒšαƒ˜αƒ—αƒ˜αƒ£αƒ›αƒ˜

...

αƒαƒ’αƒαƒœαƒ”αƒ‘αƒαƒœαƒ˜

2

αƒ‘αƒ˜αƒ›αƒ‘αƒαƒšαƒ

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

1

2

3

...

118


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

αƒ°αƒ”αƒšαƒ˜αƒ£αƒ›αƒ˜

He

2

4.0026


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

Technical information

tip

This function is available since LibreOffice 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

Please support us!