Formulas and Functions Help
- Welcome
- Intro to formulas and functions
-
- ACCRINT
- ACCRINTM
- BONDDURATION
- BONDMDURATION
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNUM
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENCYCODE
- CURRENCYCONVERT
- CURRENCYH
- DB
- DDB
- DISC
- EFFECT
- FV
- INTRATE
- IPMT
- IRR
- ISPMT
- MIRR
- NOMINAL
- NPER
- NPV
- PMT
- PPMT
- PRICE
- PRICEDISC
- PRICEMAT
- PV
- RATE
- RECEIVED
- SLN
- STOCK
- STOCKH
- SYD
- VDB
- XIRR
- XNPV
- YIELD
- YIELDDISC
- YIELDMAT
-
- AVEDEV
- AVERAGE
- AVERAGEA
- AVERAGEIF
- AVERAGEIFS
- BETADIST
- BETAINV
- BINOMDIST
- CHIDIST
- CHIINV
- CHITEST
- CONFIDENCE
- CORREL
- COUNT
- COUNTA
- COUNTBLANK
- COUNTIF
- COUNTIFS
- COVAR
- CRITBINOM
- DEVSQ
- EXPONDIST
- FDIST
- FINV
- FORECAST
- FREQUENCY
- GAMMADIST
- GAMMAINV
- GAMMALN
- GEOMEAN
- HARMEAN
- INTERCEPT
- LARGE
- LINEST
- LOGINV
- LOGNORMDIST
- MAX
- MAXA
- MAXIFS
- MEDIAN
- MIN
- MINA
- MINIFS
- MODE
- NEGBINOMDIST
- NORMDIST
- NORMINV
- NORMSDIST
- NORMSINV
- PERCENTILE
- PERCENTRANK
- PERMUT
- POISSON
- PROB
- QUARTILE
- RANK
- SLOPE
- SMALL
- STANDARDIZE
- STDEV
- STDEVA
- STDEVP
- STDEVPA
- TDIST
- TINV
- TTEST
- VAR
- VARA
- VARP
- VARPA
- WEIBULL
- ZTEST
XMATCH
The XMATCH function returns a specified item’s relative position in a range of cells.
XMATCH(search-value, search-range, match-type, search-type)
search-value: The value being searched for in search-range. search-value can contain any value or a REGEX string.
search-range: The cells to search.
match-type: An optional argument that specifies the type of match to search for.
exact or next smallest (-1): If there’s no match, returns an error.
exact match (0 or omitted): If there’s no exact match, returns an error.
exact or next largest (1): If there’s no match, returns an error.
wildcard (2): *, ? and ~ have a particular meaning. REGEX can only be used in XMATCH if you use wildcard.
search-type: An optional argument that specifies the order in which to search the range.
Binary descending (-2): Binary search that requires the range to be sorted in descending order, otherwise it returns an error.
Last to first (-1): Search the range from last to first.
First to last (1 or omitted): Search the range from first to last.
Binary ascending (2): Binary search that requires the range to be sorted in ascending order, otherwise it returns an error.
Notes
If search-range is a spanning reference (such as "B"), headers and footers are automatically ignored.
Examples |
|---|
Given the following table: |
A | B | C | |
|---|---|---|---|
1 | Name | Age | Salary |
2 | Amy | 35 | 71000 |
3 | Matthew | 27 | 81000 |
4 | Chloe | 42 | 86000 |
5 | Sophia | 51 | 66000 |
6 | Kenneth | 28 | 52000 |
7 | Tom | 49 | 62000 |
8 | Aaron | 63 | 89000 |
9 | Mary | 22 | 34000 |
10 | Alice | 29 | 52000 |
11 | Brian | 35 | 52500 |
=XMATCH(49000,C2:C11,1) searches the range for the first salary that is either exactly "49000" or is the next largest salary after "49000” and returns “5” to represent the fifth item in the range (“52000”). =XMATCH(30,B2:B11,-1) searches the range for the first employee who is exactly 30 or the next youngest age, and returns “9” to represent the ninth item in the range (“29”). =XMATCH(REGEX("^S.*"), A2:A11,2) searches the range for the first employee whose name starts with S, and returns “4" to represent the fourth item in the range (“Sophia”). |