Match

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
1Lookup valueThe value to search for.
2Lookup arrayAn array or cell range to search in.
3Match 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)
A simple Match example. We look for the exact value C in the range A1:A5. The returned value is 3.
A simple Match example. We look for the exact value C in the range A1:A5. The returned value is 3.

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 type argument when the Lookup array consists of ordered numbers.

The numbers should be sorted ascending when you use the smaller than (1) for the Match type argument and descending when you use the greater than (-1) as Match type.

Here’s an example where we use the greater than Match type with a descending range of numbers.

Using the Match function to find the position of a value greater than 45. We find 50 in row 2 so 2 is returned.
Using the Match function to find the position of a value greater than 45. We find 50 in row 2 so 2 is returned.

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:

Using Greater than as Match type with a range that is not sorted descending. A #N/A error is returned.
Using Greater than as Match type with a range that is not sorted descending. A #N/A error is returned.

An 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 is 0 (exact match).

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:

Using a Match function with the asterisk * wildcard. The wildcard allows us to match
Using a Match function with the asterisk * wildcard. The wildcard allows us to match “spread*” with “Spreadsheet Center”.

Explanation

Excel returns a 1 because “spread*” matches with “Spreadsheet Center”, which is in the first row.

Questions

Is Match case sensitive?

No, Match is not case sensitive. So if you search for HARRY you will also find Harry, hARRY and harry.

Does the returned row index start at 0 or at 1?

The returned row index starts at 1.

Can I hardcode an array to use with Match?

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.

What if there are multiple values found?

Then Match will return the position of the first one that’s found when searching top-to-bottom.

Does Match return the absolute or the relative row number?

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.

How to combine MATCH with INDEX?

You can learn how to do this in our video and blog post here.

Drawbacks

The Excel Match function has some drawbacks:

  1. The default value for Match type is 1 (smaller than or equal to) even though you probably want to use 0 (exact match) in most cases.
  2. 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.