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.

Szintaxis

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:

Példák

The table below contains a glossary for translators:

A

B

C

D

1

English

French

Italian

Japanese

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"}.

Technikai információk

tip

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