Dapatkan posisi item dalam daftar atau tabel
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])| Parameter | Deskripsi |
|---|---|
lookup_value |
The lookup value. |
lookup_array |
The array or range to search. |
match_mode |
[optional] 0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match, 3 = regex match. |
search_mode |
[optional] 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending. |
The XMATCH function performs a lookup and returns the numeric position of the lookup value as a result. XMATCH takes four arguments, and the generic s
=XMATCH(lookup_value,lookup_array,[match_mode],[search_mode])To perform an ordinary exact match, only the first two arguments are required. For example, to locate the position of the planet Mars in the worksheet
=XMATCH(G4,B5:B13) // returns 4The result is 4 since "Mars" appears in the fourth row of the range B5:B13. Typically, the XMATCH function is used together with the INDEX function to
=INDEX(C5:C13,XMATCH(G4,B5:B13)) // returns 6792In some cases, XMATCH can be a drop-in replacement for the MATCH function. For example, for exact matches, the syntax is identical:
=MATCH(value,array,0) // exact match
=XMATCH(value,array,0) // exact matchHowever, for approximate matches, the behavior is different when match_type is set to 1:
=MATCH(value,array,1) // exact match or next smallest
=XMATCH(value,array,1) // exact match or next *largest*In addition, XMATCH allows -1 for match type, which is not available with MATCH:
=XMATCH(value,array,-1) // exact match or next smallest