XMATCH function
Returns the relative position of an item in an array or range of cells.
Searches for a specified item in an array or range of cells, and then returns the item's relative position.
Syntax
XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])Square brackets indicate optional arguments.
Arguments
| Argument | Required | Meaning |
|---|---|---|
| lookup_value | Yes | The lookup valueB1 |
| lookup_array | Yes | The array or range to searchA1:A20 |
| match_mode | No | 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.0 |
| search_mode | No | 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.1 |
Try XMATCH
Find a literal asterisk in exact mode
=XMATCH(D2,A2:A4,0)Exact mode 0 treats A* as an ID. It skips AX and returns the first case-insensitive literal A* at position two.
Inspect the current formula
Read its written arguments or observe actual operation values in a separate local calculation. This action neither applies a draft nor grades an answer.
Read the formula, one argument at a time
Explore the written function calls and their inputs. The calculation result belongs to the complete formula.
XMATCH · top-level call
=XMATCH(D2,A2:A4,0)Follow the cells behind a result
Read explicit local references and helper formulas using a separate calculation. Your draft stays in this field; the worksheet and practice checks do not change.
The worksheet runs locally. Published checks apply to the supplied example only. Native Excel and Google Sheets compatibility remain unverified.
Practice a complete task
Reference and compatibility
Univer 1.0.4 / Apache-2.0. Calculation checks cover the supplied examples only. The browser worksheet does not implement every Excel feature, external service or connected data type.