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.

Sintaxia

XMATCH(Search criterion; Search Array [; Match Mode [; Search Mode ] ] )

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

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

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

0: exact match (default). If Search criterion value is not found, 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 *, ?, 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.

Adibideak

Demagun datu-base txiki bat eraiki dugula A1:DO4 gelaxka-barrutian, eta datu-base horrek 118 elementu kimikori buruzko informazioa duela. Lehen zutabeak 'Elementua', 'Ikurra', 'Zenbaki atomikoa' eta 'Masa atomiko erlatiboa' errenkada-izenburuak ditu. Gainerako zutabeek elementu bakoitzaren informazioa dute, eta elementuak ezkerretik eskuinera ordenatuta daude zenbaki atomikoaren arabera. Adibidez, B1:B4 gelaxkek 'Hidrogenoa', 'H', '1' eta '1.008' balioak dituzte, eta DPO1:DO4 gelazkek 'Oganesona', 'Og', '118' eta '294' balioak dituzte.

	A	B	C	D	...	DO
1	Elementua	Hidrogenoa	Helioa	Litioa	...	Oganesona
2	Ikurra	H	He	Li	...	Og
3	Zenbaki atomikoa	1	2	3	...	118
4	Masa atomiko erlatiboa	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.

Informazio teknikoa

This function is available since LibreOffice 24.8.

Funtzio hau ez dago Open Document Format for Office Applications (OpenDocument) Version 1.3. Part 4: Recalculated Formula (OpenFormula) Format estandarrean. Izen-espazioa honakoa da:

COM.MICROSOFT.XMATCH

XLOOKUP

XMATCH wiki page.