The Excel MATCH function searches a Lookup array (or cell range) for a given Lookup value and returns its position. If the value cannot be found, a #N/A error is returned.
Syntax
=MATCH(Lookup value, Lookup array, [Match type])
| Argument | Argument description | |
|---|---|---|
| 1 | Lookup value | The value to search for. |
| 2 | Lookup array | An array or cell range to search in. |
| 3 | Match type (optional) | Either -1, 0 or 1. 1, smaller than (default): finds values equal to or smaller than the Lookup value.0, exact match: only finds the exact Lookup value given.-1, greater than: finds values equal to or greater than the Lookup value. |
Example 1: simple Match formula
Probably the simplest way to use the Match function is like this:
=MATCH("C", A1:A5, 0)
Explanation
The result is 3. That’s because we’re searching for the letter C (the Lookup value). And the letter C is in the third row of the cell range (the Lookup array argument).
The Match type 0 is to find an exact match. Since we’re using a text lookup value, this is the most logical. We don’t want to find something ‘greater than’ or ‘smaller than’ the letter C. We want to find exactly C.
About MATCH in combination with INDEX
When you’ve been using Excel for a bit longer, what you’ll see is that the Match function is very often combined with the Index function. That’s because the two functions work like a better Vlookup. There’s a lot more to this than meets the eye, so we created a video and blog post all about this.
Example 2: Match type argument
You can use the Match typeLookup array consists of ordered numbers.
The numbers should be sorted ascending when you use the smaller than (1) for the Match typeMatch type
Here’s an example where we use the greater than Match type
Explanation
We are looking for a value greater than (or equal to) 45. We find the value (50) in row 2. So 2 is returned.
It is interesting to see what happens if we do not order the cell range descending:
Match typeAn error is shown! And that is in the best case. Sometimes, Excel simply returns a wrong number. So make sure you order your cell ranges if you want to use the Match function this way.
Example 3: Wildcards
The Match function accepts the use of wildcards when Match type
Wildcards are characters like ? and * that allow you to search for an approximate match instead of an exact word match.
Note: for more information on wildcards, check this section about wildcards from our Find and Replace post.
We can use for example the asterisk * with the Match function like this:
Explanation
Excel returns a 1 because “spread*” matches with “Spreadsheet Center”, which is in the first row.
Questions
No, Match is not case sensitive. So if you search for HARRY you will also find Harry, hARRY and harry.
The returned row index starts at 1.
Yes, you can. To write this array, use curly braces ({, }) around the values and separate them using commas. Like this:
=MATCH(6, {2, 4, 6, 8}, 0), which will return 3.
Then Match will return the position of the first one that’s found when searching top-to-bottom.
Match returns the row number relative to the cell range given. If you give it a range starting at C3 and the found value is in C3, the row number returned will be 1.
You can learn how to do this in our video and blog post here.
Drawbacks
The Excel Match function has some drawbacks:
- The default value for
Match type - More of a general note than a drawback, but Match returns the position of a value. It will not return an actual value itself. If you want to return values, you need to combine Match with (for example) the Index function.