﻿ XMATCH | Spread.NET 17 Formula Reference
Formula Functions / Functions T to Z / XMATCH
In This Topic
XMATCH
In This Topic

XMATCH function searches for a specific item in an array or a range of cells and then returns the relative position of the item. In other words, this function can be used to find the position of an item in a list.

XMATCH function provides support for approximate matching, partial matching (using widcard characters like * , ? etc.) and exact matching.

#### Syntax

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

#### Arguments

This function has the following arguments:

Argument Description
lookup_value Refers to the lookup value.
lookup_array Refers to the lookup array or cell range that you want to search.
[match_mode]

[Optional] Specifies the type of the match as per the following values:

0 -  Refers to exact match. If no matches are found, then #N/A error is returned. This is the default match mode.

-1 - Refers to exact match. If no matches are found, then the next smaller item is returned.

1 -  Refers to exact match. If no matches are found, then the next larger item is returned.

2 -  Refers to a wildcard match where *, ? and ~ characters possess special meanings to indicate partial match.

[search_mode]

[Optional] Specifies the mode of the search as per the following values:

0 -  Refers to the "search all" mode where all the matched values will be returned. [This mode is not available in Excel.]

1 -  Refers to a search that starts at the first item. This is the default search mode.

-1 - Refers to a reverse search that starts at the last item.

2  -  Refers to a binary search that depends upon the lookup_array argument being sorted in the ascending order.

-2 -  Refers to a binary search that depends upon the lookup_array argument being sorted in the descending order.

#### Remarks

The XMATCH function can work with both vertical and horizontal arrays. This functions returns #N/A error if the lookup value is not found.

#### Data Types

Accepts numeric data. Looks up values in a range or table. Returns an array with multiple items.

#### Examples

=XMATCH(E3,C3:C7,1)

=XMATCH(F2,C3:C9,1)

=(INDEX(C6:C14,XMATCH(G5,B6:B14))

#### Version Available

This function is available in product version 13.0 or later.