XMATCH

說明

Use this function to search for a specified item in an array or range of cells and return the item's relative position.

XMATCH is an advanced version of the MATCH function. It can perform exact or approximate matches and can search in ascending or descending order.

XMATCH differs from MATCH in several ways:

XMATCH supports binary search for faster performance on large, sorted datasets
XMATCH allows for wild card characters in exact match mode
XMATCH can return the next larger or smaller item if an exact match is not found

XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

This function accepts the following arguments:

名稱	必要	說明	Valid input
`lookup_value`	是	The value to search for in the lookup_array.	A number, a text string, a logical value, a cell reference, a cell range, or a formula which results in any of these.
`lookup_array`	是	The range of cells being searched.	A cell reference, a cell range, or a formula which results in either of these.
`match_mode`	沒有	How to match `lookup_value`.	0 (exact match), [Default] -1 (exact match or next smaller item) 1 (exact match or next larger item) 2 (wildcard match) A formula which results in any of these is also acceptable.
`search_mode`	沒有	The search mode to use.	1 (search first-to-last), [Default] -1 (search last-to-first) 2 (binary search ascending) -2 (binary search descending). A formula which results in any of these is also acceptable.

公式	說明	結果
`=XMATCH("Cherry", A2:A7)`	Finds the position of "Cherry" in the product list.	3
`=XMATCH(1, B2:B7, 1)`	Finds the position of 1 or the next larger value in the price list.	4
`=XMATCH("G*", A2:A7, 2)`	Searches for a product starting with "G" using wildcard.	#N/A
`=XMATCH("Date", A2:A7, 0, -1)`	Searches for "Date" from the bottom of the list.	4
`=XMATCH(1.5, B2:B7, -1)`	Finds the position of 1.5 or the next smaller value in the price list.	4

XMATCH returns the relative position of the matched item in the lookup_array.
If no match is found and an approximate match is not specified, XMATCH returns #N/A.
For binary search (search_mode 2 or -2), the lookup_array must be sorted in ascending or descending order.
Wildcard characters (* and ?) are only supported when match_mode is set to 2.
XMATCH is case-insensitive when matching text values.

Use XMATCH with XLOOKUP for powerful, flexible lookup operations.
For large sorted datasets, use binary search (search_mode 2 or -2) for better performance.
When working with unsorted data, use search_mode 1 or -1 to ensure accurate results.
You can combine XMATCH with other functions such as INDEX for advanced data retrieval tasks.

CELL
CHILDREFS
HLOOKUP
INDEX
MATCH
VLOOKUP
XLOOKUP
XMATCH