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 (* ?) 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.

Sintaxi

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

Lookup: The value of any type to search for in Array. If omitted, XLOOKUP returns blank cells it finds in Array.

Array: is the reference of the array or range to search.

Return: is the reference of the array or range to return.

note

If Return is an array of values, the XLOOKUP function must be entered as an array formula.


NotFound: a text to return if the Lookup value is not found. If a valid match is not found, and NotFound is omitted, returns #N/A.

MatchType: Specify the match type:

SearchMode: Specify the search mode to use:

Exemples

La taula següent conté un glossari per a traductors:

A

B

C

D

1

Anglès

Francès

Italià

Japonès

2

New

Nouveau

Nuovo

新しい

3

Save

Enregistrer

Salva

保存

4

Open

Ouvrir

Apri

開ける


={XLOOKUP("New", A2:A4, B2:D4,"Missing")} returns the array {"Nouveau","Nuovo","新しい"}.

={XLOOKUP("Reload", A2:A4, B2:D4,"Missing")} returns the array {"Missing","Missing","Missing"}.

Informació tècnica

tip

Aquesta funció és disponible des de la versió 24.8 del LibreOfficeDev.


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