# ​​​​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`])

## 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 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.

### 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 `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:

### 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.