In Excel, the Match function searches for a value in an array and returns the relative position of that item.
The syntax for the Match function is:
Match( value, array, match_type )
value is the value to search for in the array.
array is a range of cells that contains the value that you are searching for.
match_type is optional. It the type of match that the function will perform. The possible values are:
match_type Explanation 1
(default)The Match function will find the largest value that is less than or equal to value. You should be sure to sort your array in ascending order. If the match_type parameter is omitted, the Match function assumes a match_type of 1.
0 The Match function will find the first value that is equal to value. The array can be sorted in any order. -1 The Match function will find the smallest value that is greater than or equal to value. You should be sure to sort your array in descending order.
Note:
The Match function does not distinguish between upper and lowercase when searching for a match.
If the Match function does not find a match, it will return a #N/A error.
If the match_type parameter is 0 and a text value, then you can use wildcards in the value parameter.
Wild card Explanation * matches any sequence of characters ? matches any single character
Applies To:
- Excel 2007, Excel 2003, Excel XP, Excel 2000
For example:
Let's take a look at an example:
Based on the Excel spreadsheet above:
=Match(10572, A2:A5, 1) would return 3
(it matches on 10571 since the match_type parameter is set to 1)=Match(10572, A2:A5) would return 3
(it matches on 10571 since the match_type parameter has been omitted and will default to 1)=Match(10572, A2:A5, 0) would return #N/A
(it doesn't find a match since the match_type parameter is set to 0)=Match(10573, A2:A5, 1) would return 4 =Match(10573, A2:A5, 0) would return 4
Let's take a look at how we can use wild cards in the Match function.
Based on the Excel spreadsheet above:
| =Match("I?M", A2:A5, 0) | would return 1 |
| =Match("M*t", A2:A5, 0) | would return 2 |
| =Match("M?t", A2:A5, 0) | would return #N/A |
