XMATCH
Description
Searches for a specified item in the list (one-dimensional array) that you specify, and returns the item's index (position). XMATCH does an exact match search by default. You can use this function to match logical values, numeric values, or text strings. This function is intended for use with unconstrained arrays. To submit the formula, use the unconstrained keyboard shortcut Ctrl+Alt+Enter (Windows) or Command+Option+Enter (Mac). For syntax details, see the Function Reference in the User Guide.
Syntax
XMATCH(
lookup_value
, lookup_array
, [ match_mode
], [ search_mode
]) - lookup_value: The lookup value.
- lookup_array: The array to search.
- match_mode: A number that specifies the type of match.
- 0: Exact match. If not found, return #N/A. This is the default.
- -1: Exact match. If not found, return the next smaller item.
- 1: Exact match. If not found, return the next larger item.
- 2: A wildcard match. In text-based criteria, you can use the ? wildcard to match any single character or the * wildcard to match any sequence of characters. If the value to find contains a * or ?, precede it with a tilde (~).
- search_mode: A number that specifies the search mode to use.
- 1: Do a search starting with the first item. This is the default.
- -1: Do a reverse search starting with the last item.
- 2: Do a binary search that relies on lookup_array being sorted in ascending order. If it isn't sorted, the results won't be valid.
- -2: Do a binary search that relies on lookup_array being sorted in descending order. If it isn't sorted, the results won't be valid.
Notes
- XMATCH isn't case-sensitive by default (using a match_mode of 0). You can use XMATCH for a case-sensitive exact match search only if the array is sorted.
Related Functions
MATCH
MATCHEXACT