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

argument when the `Match type`

`Lookup array`

consists of ordered numbers.

The numbers should be sorted **ascending **when you use the smaller than (1) for the

argument and `Match type`

**descending **when you use the greater than (-1) as

.`Match type`

Here’s an example where we use the greater than

with a descending range of numbers.`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:

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

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

**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:

- The default value for

is 1 (smaller than or equal to) even though you probably want to use 0 (exact match) in most cases.`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.