# Excel MATCH Function: Syntax and Examples

The Excel MATCH function returns the relative position of a value inside a range or array. If the range A1:A3 holds 5, 25, and 38, then `=MATCH(25,A1:A3,0)` returns 2 because 25 is the second item [1]. You use MATCH when you need the location of an item instead of the item itself, most often to feed the row_num argument of INDEX [1].

## Quick Answer

- MATCH returns a position number, not the matched value.
- The syntax is `MATCH(lookup_value, lookup_array, [match_type])` [1].
- `lookup_value` is required and can be a number, text, logical value, or a cell reference to one [1].
- `lookup_array` is required and is the single row or column being searched [1].
- `match_type` is optional and takes the value -1, 0, or 1. The default is 1 [1].

## Syntax

| Argument | Required? | Meaning |
|---|---|---|
| `lookup_value` | Required | The value you want to find in `lookup_array`. It can be a number, text, logical value, or a reference to one [1]. |
| `lookup_array` | Required | The range of cells being searched [1]. |
| `match_type` | Optional | The number -1, 0, or 1. It controls how Excel matches `lookup_value` against the values in `lookup_array`. The default is 1 [1]. |

The `match_type` setting changes the behavior completely:

| match_type | What Excel finds | Sorting requirement |
|---|---|---|
| 1 (or omitted) | The largest value less than or equal to `lookup_value` | `lookup_array` must be in ascending order [2] |
| 0 | The first value exactly equal to `lookup_value` | No sorting needed |
| -1 | The smallest value greater than or equal to `lookup_value` | `lookup_array` must be in descending order [2] |

For most day-to-day lookups you want `match_type` set to 0. It finds an exact match and does not care how the data is sorted.

## How It Works

MATCH walks through `lookup_array` and compares each cell against `lookup_value`. With `match_type` at 0, it stops at the first cell that equals the lookup value and returns that cell's position within the range, counting from 1.

Position is relative to the range you pass in, not to the worksheet. If you search `A2:A6` and the match sits in cell A3, MATCH returns 2, because A3 is the second cell of that range. This is the detail that trips people up most often.

With `match_type` at 1, MATCH assumes the data is sorted ascending and returns the position of the largest value that is less than or equal to the lookup value. With `match_type` at -1, it assumes descending order and returns the smallest value greater than or equal to the lookup value [2]. Both approximate modes depend on correct sorting, and wrong sorting produces wrong answers or errors.

MATCH is usually paired with INDEX. The pattern `=INDEX(Table_Array,MATCH(Lookup_Value,Lookup_Array,0),Col_Index_Num)` finds a value in one column and returns the matching value from another column in the same row [3]. If no cell in `Lookup_Array` matches, that formula returns #N/A [3].

## Worked Example

The sheet below lists five products with prices. Column C uses MATCH to report where each product name sits in the range A2:A6.

| | A | B | C |
|---|---|---|---|
| 1 | Product | Price | Position |
| 2 | Gadget | 10.5 | `=MATCH("Widget",A2:A6,0)  -> displays 2` |
| 3 | Widget | 15.75 | `=MATCH("Gadget",A2:A6,0)  -> displays 1` |
| 4 | Gizmo | 8.99 | `=MATCH("Gizmo",A2:A6,0)  -> displays 3` |
| 5 | Doohickey | 22 | `=MATCH("Doohickey",A2:A6,0)  -> displays 4` |
| 6 | Thingamajig | 5.25 | `=MATCH("Thingamajig",A2:A6,0)  -> displays 5` |

Each formula returns the position of the product inside A2:A6:

- C2 finds "Widget" and returns 2.
- C3 finds "Gadget" and returns 1.
- C4 finds "Gizmo" and returns 3.
- C5 finds "Doohickey" and returns 4.
- C6 finds "Thingamajig" and returns 5.

Notice that "Gadget" sits in cell A2 but MATCH returns 1, not 2. The count starts at the first cell of the range you gave it. If you searched A1:A6 instead, "Gadget" would return 2.

## More Examples

**Exact match on a number.** With prices in B2:B6, `=MATCH(22,B2:B6,0)` returns 4, because 22 is the fourth price in that range.

**Approximate match on sorted grades.** Suppose A2:A6 holds 50, 60, 70, 80, 90 in ascending order. `=MATCH(75,A2:A6,1)` returns 3, the position of 70, the largest value less than or equal to 75. This mode is useful for banding scores into grade thresholds.

**Descending match.** If A2:A6 holds 90, 80, 70, 60, 50 in descending order, `=MATCH(75,A2:A6,-1)` returns 2, the position of 80, the smallest value greater than or equal to 75.

**Wildcards with match_type 0.** When `match_type` is 0, `lookup_value` can contain the wildcard characters `*` and `?`. `=MATCH("G*",A2:A6,0)` returns 1, the first product name starting with G. The tilde `~` escapes a literal wildcard character.

**Feeding INDEX.** `=INDEX(B2:B6,MATCH("Gizmo",A2:A6,0))` returns 8.99. MATCH supplies the row position and INDEX returns the price from that row.

**Two-way lookup.** Combining INDEX with two MATCH calls lets you look up a value by row and column at the same time. The newer [XMATCH function](/blog/data-analysis/xmatch-function-excel) does the same job with fewer arguments and searches in any direction [4].

## Errors and How to Fix Them

**#N/A.** MATCH returns #N/A when it cannot find the lookup value in the lookup array [2]. Check for trailing spaces, numbers stored as text, and case differences in text that only look identical. If you want a friendlier result, wrap the formula in IFERROR, but confirm the formula works correctly first, because replacing #N/A only hides the error and does not resolve it [2].

**#N/A from wrong sorting.** If `match_type` is 1 or omitted, the values in `lookup_array` should be in ascending order. If `match_type` is -1, they should be in descending order [2]. A descending list searched with `match_type` 1 can produce #N/A or a wrong position. Either change `match_type` to -1 or sort the table in ascending order [2].

**#VALUE!.** If you use INDEX as an array formula together with MATCH to retrieve a value, the formula needs to be entered as an array formula, otherwise you see #VALUE! [5]. In Excel for Microsoft 365, dynamic arrays remove the need for legacy array-entry keystrokes for many formulas [5].

## Common Mistakes

- **Expecting the value instead of the position.** MATCH returns a number like 3, not the cell contents. Wrap it in INDEX when you want the value.
- **Forgetting that positions are relative.** `MATCH("Gadget",A2:A6,0)` returns 1 even though the value sits in row 2. Count from the first cell of the range you passed in.
- **Leaving `match_type` out.** The default is 1, which is an approximate match on sorted data [1]. If you want an exact match, type the 0 explicitly.
- **Searching unsorted data with `match_type` 1 or -1.** Approximate modes assume sorted data and return wrong positions or #N/A when the order is wrong [2].
- **Mixing text and numbers.** A number stored as text will not match a real number. Convert the column or use a formula that coerces the type.
- **Using MATCH on a two-dimensional range.** `lookup_array` should be a single row or column. For a grid, use one MATCH for the row and one for the column.

## Limitations

MATCH returns only the first matching position. If a value appears several times, you get the position of the first occurrence in the search order, and there is no built-in way to ask for the second or last one with the classic function.

The approximate modes are fragile. They depend entirely on the data being sorted in the direction the `match_type` expects, and Excel does not warn you when it is not. A wrong sort order can return a plausible but incorrect position, which is harder to spot than an outright error. The newer XMATCH function avoids some of this by returning exact matches by default and searching in any direction [4]. For counting how many times a value appears instead of where it appears, a function like [COUNTIF](/blog/data-analysis/countif-function-excel) is the better tool.

## Frequently Asked Questions

### What is the difference between MATCH and VLOOKUP?

VLOOKUP returns a value from a table. MATCH returns the position of a value in a single row or column. You often combine them, using MATCH to supply the column index for VLOOKUP or the row number for INDEX. MATCH is more flexible because it can search in any direction.

### Why does my MATCH formula return #N/A when the value is clearly there?

The most common causes are trailing spaces, numbers stored as text, or a `match_type` that does not suit the data order. Confirm the lookup value and the cell contents are the same data type, and set `match_type` to 0 for an exact match [2].

### Does MATCH care about uppercase and lowercase?

No. MATCH treats text comparisons as case-insensitive, so "widget" and "Widget" match each other. If you need a case-sensitive match, you need a different approach, such as combining MATCH with the EXACT function inside an array formula.

### What does the match_type argument actually do?

It tells Excel how to compare the lookup value against the range. A value of 0 finds an exact match. A value of 1 finds the largest value less than or equal to the lookup value in ascending data. A value of -1 finds the smallest value greater than or equal to the lookup value in descending data [1].

### Can MATCH search from the bottom of a range upward?

The classic MATCH function searches in one direction only. The XMATCH function adds a search_mode argument, where -1 searches last-to-first, so you can find the last occurrence of a value in a list [4]. If you need that behavior, XMATCH is the simpler choice.

## References

1. [MATCH function | Microsoft Support](https://support.microsoft.com/en-us/excel/functions/match-function)
2. [How to correct a #N/A error in INDEX/MATCH functions | Microsoft Support](https://support.microsoft.com/en-us/excel/how-to-correct-a-n-a-error-in-index-match-functions)
3. [Use Excel built-in functions to find data in a table or a range of cells | Microsoft Support](https://support.microsoft.com/en-us/excel/use-excel-built-in-functions-to-find-data-in-a-table-or-a-range-of-cells)
4. [XMATCH function | Microsoft Support](https://support.microsoft.com/en-us/excel/functions/xmatch-function)
5. [How to correct a #VALUE! error in INDEX/MATCH functions | Microsoft Support](https://support.microsoft.com/en-us/excel/how-to-correct-a-value-error-in-index-match-functions)

## Further Reading

- [Broman KW, Woo KH (2018). Data Organization in Spreadsheets. The American Statistician](https://doi.org/10.1080/00031305.2017.1375989)
- [Ziemann M, Eren Y, El-Osta A (2016). Gene name errors are widespread in the scientific literature. Genome Biology](https://doi.org/10.1186/s13059-016-1044-7)

## Related Articles

- [XMATCH Function in Excel: Syntax and Examples](/blog/data-analysis/xmatch-function-excel)
- [Excel FILTER Function: Syntax and Examples](/blog/data-analysis/excel-filter-function-syntax-examples)
- [Excel FIND Function: Syntax, Examples and SEARCH Comparison](/blog/data-analysis/excel-find-function-syntax-examples)
- [Excel TEXT Function: Syntax, Format Codes and Examples](/blog/data-analysis/excel-text-function)
- [Excel RIGHT Function: Syntax, Examples and Uses](/blog/data-analysis/excel-right-function)