XMATCH

Searches for a value in a one-dimensional array and returns the relative position of the item.

XMATCH outperforms function MATCH as it allows searches according to the search mode.

Syntaxe

XMATCH(Lookup; Array [; MatchType [; SearchMode ] ] )

Lookup: The value of any type to search for in Array.

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

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

0: exact match (default). If Lookup value is not found, then return the #N/A error.
-1: attempt exact match. If Lookup value is not found, then return the next smaller item.
1: attempt exact match. If Lookup value is not found, then return the next larger item.
2: a wildcard match where characters *, ?, 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.

Exemples

Supposons que nous ayons construit une petite table de base de données occupant la plage de cellules A1:DO4 et contenant des informations de base sur 118 éléments chimiques. La première colonne contient les en-têtes de ligne " Élément ", " Symbole ", " Numéro atomique " et " Masse atomique relative ". Les colonnes suivantes contiennent les informations pertinentes pour chacun des éléments, classées de gauche à droite par numéro atomique. Par exemple, les cellules B1:B4 contiennent "Hydrogène", "H", "1" et "1,008", tandis que les cellules DO1:DO4 contiennent "Oganesson", "Og", "118" et "294".

	A	B	C	D	...	DO
1	Élément	Hydrogène	Hélium	Lithium	...	Oganesson
2	Symbole	H	He	Li	...	Og
3	Numéro atomique	1	2	3	...	118
4	Masse atomique relative	1.008	4.0026	6.94	...	294

=XMATCH("Atomic Number",A1:A4) returns 3, the third line of the A1:A4 array.

=XMATCH("Li",A2:DO2) returns 4, the fourth column of the A2:DO2 array.

Informations techniques

This function is available since LibreOffice 24.8.

Cette fonction ne fait pas partie de Open Document Format for Office Applications (OpenDocument) Version 1.3. Partie 4 : Norme de format de formule recalculée (OpenFormula). L'espace de nom est

COM.MICROSOFT.XMATCH

XLOOKUP

XMATCH wiki page.