MATCH
Description
Returns the relative position of an item in a range that matches a specified value.
Syntax
MATCH(search_key, range, search_type)
search_key
- The item that the formula searches on within the specified range of cells. It can be a pure text (‘Evan‘), or a cell reference (like A7), or even a function that returns a string or a number (like LEFT(“Mike Johnson”,8) or DATE(2017,1,1))range
- The one-dimensional array to be searched.search_type
- [ OPTIONAL, may be 1, 0, or -1, set to -1
by default ] - Defines the manner in which thr search is performed.- 1 - the default, causes
MATCH
to assume that the range is sorted in ascending order and return the largest value less than or equal tosearch_key
.
- 1 - the default, causes
0
- indicates exact match, and is required in situations whererange
is not sorted.-1
- causesMATCH
to assume that the range is sorted in descending order and return the smallest value greater than or equal tosearch_key
.
Note: If a range with both height and width greater than 1 is used, MATCH
will return #N/A!
.
Sample Usage
MATCH("Sunday",A2:A9,0)
MATCH(DATE(2012,1,1),A2:F2)
How to use MATCH
In order to use the MATCH formula, start with your edited Excellentable:
Then type in the MATCH Formula in the area you would like to display the outcome:
By adding the values you would like to calculate the MATCH formula for, Excellentable will generate the outcome:
A
|
B
|
C
|
D
|
|
---|---|---|---|---|
1
|
||||
2
|
||||
3
|
||||
4
|
||||
5
|
||||
6
|
||||
7
|
||||
8
|
||||
9
|
||||
10
|
||||
11
|
||||
12
|
||||
13
|
||||
14
|
||||
15
|
E
|
F
|
|
---|---|---|
1
|
Notes
The MATCH
formula does not return the value iteself, rather it returns the position in an array or range of a matched value. To return the value itself or another value corresponding to the row or column the match is found in, use the INDEX
, HLOOKUP
, or VLOOKUP formulas instead.
See Also
VLOOKUP
: Vertical lookup. Searches down the first column of a range for a key and returns the value of a specified cell in the row found.
HLOOKUP
: Horizontal lookup. Searches across the first row of a range for a key and returns the value of a specified cell in the column found.
INDEX
: Returns the content of a cell, specified by row and column offset.