# XMATCH function

The XMATCH function searches for a specified item in an array or range of cells, and then returns the item's relative position.

Here we'll use XMATCH to find the position of an item in a list.

## Syntax

The XMATCH function returns the relative position of an item in an array or range of cells.

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

Argument

Description

lookup_value

Required

The lookup value

lookup_array

Required

The array or range to search

[match_mode]

Optional

Specify the match type:

0 - Exact match (default)

-1 - Exact match or next smallest item

1 - Exact match or next largest item

2 - A wildcard match where *, ?, and ~ have special meaning.

[search_mode]

Optional

Specify the search type:

1 - Search first-to-last (default)

-1 - Search last-to-first (reverse search).

2 - Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.

-2 - Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.

## Examples

### Example 1

The following example finds the position of the first term that is an exact match or the next largest value for (i.e., starts with) "Gra".

### Example 2

This next example finds the number of sales people eligible for a bonus. This also uses 1 for the match_mode to find an exact match or the next largest item in the list, but since the data is numeric it returns a count of values. In this case, the function returns 4, since there are 4 sales reps who exceeded the bonus amount.

### Example 3

Next, we'll use a combination of INDEX/XMATCH/XMATCH to perform a simultaneous vertical and horizontal lookup. In this case, we want to return the sales amount for a given sales rep and a given month. This is similar to using the INDEX and MATCH functions in conjunction, except that it requires fewer arguments.

### Example 4

You can also use XMATCH to return a value in an array. For example, =XMATCH(4,{5,4,3,2,1}) would return 2, since 4 is the second item in the array. This is an exact match scenario, whereas =XMATCH(4.5,{5,4,3,2,1},1) returns 1, as the match_mode argument (1) is set to return an exact match or the next largest item, which is 5.

## Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.