Formula Reference
Contacting Us
Getting Technical Support
Formula Overview
Formula Functions
- Functions A to C
- Functions D to G
- Functions H to L
- Functions M to Q
- Functions R to S
- Functions T to Z
  - T
  - T.DIST
  - T.DIST.2T
  - T.DIST.RT
  - T.INV
  - T.INV.2T
  - T.TEST
  - TAKE
  - TAN
  - TANH
  - TBILLEQ
  - TBILLPRICE
  - TBILLYIELD
  - TDIST
  - TEXT
  - TEXTAFTER
  - TEXTBEFORE
  - TEXTJOIN
  - TEXTSPLIT
  - TIME
  - TIMEVALUE
  - TINV
  - TOCOL
  - TODAY
  - TOROW
  - TRANSPOSE
  - TREND
  - TRIM
  - TRIMMEAN
  - TRUE
  - TRUNC
  - TTEST
  - TYPE
  - UNICHAR
  - UNICODE
  - UNIQUE
  - UPPER
  - USDOLLAR
  - VALUE
  - VALUETOTEXT
  - VAR
  - VAR.P
  - VAR.S
  - VARA
  - VARISPARKLINE
  - VARP
  - VARPA
  - VBARSPARKLINE
  - VDB
  - VLOOKUP
  - VSTACK
  - WEBSERVICE
  - WEEKDAY
  - WEEKNUM
  - WEIBULL
  - WEIBULL.DIST
  - WINLOSSSPARKLINE
  - WORKDAY
  - WORKDAY.INTL
  - WRAPCOLS
  - WRAPROWS
  - XIRR
  - XLOOKUP
  - XMATCH
  - XNPV
  - XOR
  - YEAR
  - YEARFRAC
  - YEARSPARKLINE
  - YIELD
  - YIELDDISC
  - YIELDMAT
  - Z.TEST
  - ZTEST

XMATCH

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.