Document Solutions for Excel, .NET Edition Overview
Key Features
Getting Started
Features
Templates
Formula Reference
- Add-in and Automation Functions
- Compatibility Functions
- Database Functions
- Date and Time Functions
- Engineering Functions
- Financial Functions
- Information Functions
- Logical Functions
- Lookup and Reference Functions
  - ADDRESS
  - AREAS
  - CHOOSE
  - CHOOSECOLS
  - CHOOSEROWS
  - COLUMN
  - COLUMNS
  - DROP
  - EXPAND
  - FILTER
  - FORMULATEXT
  - GETPIVOTDATA
  - GROUPBY
  - HLOOKUP
  - HSTACK
  - HYPERLINK
  - IMAGE
  - INDEX
  - INDIRECT
  - LOOKUP
  - MATCH
  - OFFSET
  - PIVOTBY
  - ROW
  - ROWS
  - SINGLE
  - SORT
  - SORTBY
  - TAKE
  - TOCOL
  - TOROW
  - TRANSPOSE
  - TRIMRANGE
  - UNIQUE
  - VLOOKUP
  - VSTACK
  - WRAPCOLS
  - WRAPROWS
  - XLOOKUP
  - XMATCH
- Math and Trigonometry Functions
- Statistical Functions
- Text Functions
- Web Functions
File Operations
Document Solutions Data Viewer
API Reference
Release Notes

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 wildcard 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	[Required] Refers to the lookup value.
lookup_array	[Required] 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 the #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 a 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 ascending order. -2 - Refers to a binary search that depends upon the lookup_array argument being sorted in descending order.

Remarks

The XMATCH function can work with both vertical and horizontal arrays. This function returns the #N/A error if the lookup value is not found. XMATCH will return #VALUE! if using wildcard match mode and binary search mode at the same time.

Examples

XMATCH(G4,A5:A9)

XMATCH({5,4,1},C5:C9)