The Excel XMATCH function performs a lookup and returns a position in vertical or horizontal ranges. It is a more robust and flexible successor to the MATCH function. XMATCH supports approximate and exact matching, reverse search, and wildcards (* ?) for partial matches.

**Syntax**:= XMATCH (lookup_value, lookup_array, [match_mode], [search_mode])

The XMATCH function syntax has the following arguments:

**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.**search_mode**– [optional] 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending.

### Match type

The third argument for XLOOKUP is **match_type**. This is an *optional* argument that controls match behavior as follows:

### Search mode

### The fourth argument for XLOOKUP is **search_mode**. This is an *optional* argument that controls search behavior as follows:

**Example**: Let’s look at some Excel XMATCH function examples and explore how to use the XMATCH function as a worksheet function in Microsoft Excel:Binary searches are very fast, but take care data is sorted as required. If data is not sorted properly, a binary search can return invalid results that look perfectly normal.

**Example 1: Exact match**

In the example shown, XMATCH is used to retrieve the position in a list of the range A2:A17. The formula is:

**Syntax**: =XMATCH(G2;A2:A17)

**Result**:

Based on the Excel spreadsheet above, the following XMATCH examples would return:

**Syntax**: =XMATCH(G2;A2:A17)

**Result**: 6

**Syntax**: =XMATCH(G3;A2:A17)

**Result**: 10

**Syntax**: =XMATCH(G4;A2:A17)

**Result**: 11

Notice XMATCH defaults to an exact match. If “Jack Albertson” G2 was misspelled “Jack Albertsol”, XMATCH would return #N/A.

**Example 2: Match behavior**

**Syntax**: =XMATCH(G2;B2:B17;0)

**Result**:

Based on the Excel spreadsheet above, the following XMATCH examples would return:

**Syntax**: =XMATCH(G2;B2:B17;0)

**Result**: 3

**Syntax**: =XMATCH(G3;B2:B17;0)

**Result**: #N/A

**Syntax**: =XMATCH(G4;B2:B17;2)

**Result**: 5

**Syntax**: =XMATCH(G5;B2:B17;-1)

**Result**: 2

**Syntax**: =XMATCH(G6;B2:B17;1)

**Result**: 3

**Example 3: INDEX and XMATCH**

XMATCH can be used just like MATCH with the INDEX function. To retrieve the age of Jack Albertson based on the original example above, the formula is:

**Syntax**: =INDEX(D2:D17;XMATCH(G2;A2:A17))

**Result**:

Based on the Excel spreadsheet above, the following XMATCH examples would return:

**Syntax**: =INDEX(D2:D17;XMATCH(G2;A2:A17))

**Result**: 30

**Syntax**: =INDEX(D2:D17;XMATCH(G3;A2:A17))

**Result**: 27

**Syntax**: =INDEX(D2:D17;XMATCH(G4;A2:A17))

**Result**: 50

**Note**:

- XMATCH can work with both vertical and horizontal arrays.
- XMATCH will return #N/A if the lookup value is not found.