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.

Sintaxe

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

Pódese omitir calquera dos argumentos opcionais. Un argumento opcional require que todos os separadores presentes estean presentes.

Exemplos

Supoña que construímos unha pequena táboa de base de datos que ocupa o intervalo de celas A1:DO4 e contén información básica sobre 118 elementos químicos. A primeira columna contén as cabeceiras de fila «Elemento, «Símbolo», «Número atómico» e «Masa atómica relativa». As columnas subseguintes conteñen a información relevante para cada elemento, ordenada de esquerda a dereita polo número atómico. Por exemplo, as celas B1:B4 conteñen «Hidróxeno», «H», «1» e «1,008» mentres que as celas DO1:DO4 conteñen «Organesson», «Og», «118» e «294».

	A	B	C	D	...	DO
1	Elemento	Hidróxeno	Helio	Litio	...	Organesson
2	Símbolo	H	He	Li	...	Og
3	Número atómico	1	2	3	...	118
4	Masa atómica relativa	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"}.

Información técnica

Esta función está dispoñíbel desde a versión 24.8 do LibreOffice.

Esta función NON é parte do estándar Formato Open Document para Aplicacións de Ofimática (OpenDocument), versión 1.3. Parte 4: Fórmula recalculada (OpenFormula). O espazo de nomes é

COM.MICROSOFT.XLOOKUP

XMATCH

XLOOKUP wiki page.