# MATCH function in Excel

> MATCH returns the position of a value in a row or column: 3 means the third cell.

Source: https://spreadsheetpractice.com/functions/match · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** MATCH returns the position of a value in a row or column: 3 means the third cell.
- **Syntax:** `=MATCH(lookup_value, lookup_array, [match_type])`
- **Most common use:** `=MATCH(E2,A2:A5,0)` returns 3 (Stapler is the third item in the product list)
- **Available in:** Every current version
- **Category:** [Lookup functions](https://spreadsheetpractice.com/functions/category/lookup)
- **Returns:** A position number counted from the start of lookup_array, or #N/A when there is no match.
- **Google Sheets:** Works the same way in Google Sheets.

## What it does

MATCH returns the position of a value in a row or column: 3 means the third cell. On its own it answers "where is it?" Paired with INDEX it becomes a lookup that works in every Excel version, can look left, and keeps working when columns are inserted.

## Syntax

`=MATCH(lookup_value, lookup_array, [match_type])`

| Argument | Required? | What it is |
|---|---|---|
| `lookup_value` | Required | The value to find. |
| `lookup_array` | Required | The single row or column to search. |
| `match_type` | Optional | 0 for an exact match. 1, the default, finds the largest value less than or equal to lookup_value in a list sorted smallest to largest; -1 finds the smallest value greater than or equal to it in a list sorted largest to smallest. |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| F2 | `=MATCH(E2,A2:A5,0)` | 3 | Stapler is the third item in the product list |
| F3 | `=INDEX(C2:C5,MATCH(E2,A2:A5,0))` | 85 | Paired with INDEX: the stock for Stapler |
| F4 | `=MATCH("Price",A1:C1,0)` | 2 | Works across a row too: Price is the second header |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Stock |  | Find | Result |
| 2 | Notebook | $4.50 | 320 |  | Stapler | 3 |
| 3 | Pen pack | $6.00 | 540 |  |  | 85 |
| 4 | Stapler | $14.99 | 85 |  |  | 2 |
| 5 | Binder | $7.25 | 210 |  |  |  |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| Wrong position with no error | match_type was left out, so MATCH did an approximate match, which needs sorted data. Use 0 for exact matches. |
| Position looks off by one | MATCH counts from the first cell of lookup_array, not from row 1 of the sheet. If the range starts at A2, the item in A2 is position 1. |
| #N/A | The value isn't in the list. Check for trailing spaces, or a number stored as text in one place and as a number in the other. |

## Tips

- MATCH accepts wildcards with match_type 0: =MATCH("Sta*",A2:A5,0).
- Match the header row to pick a column by name: =INDEX(A2:C5,MATCH(E2,A2:A5,0),MATCH("Stock",A1:C1,0)).

## Related functions

- [INDEX](https://spreadsheetpractice.com/functions/index): INDEX returns the value at a given row and column of a range: INDEX(A2:C10,4,2) is the fourth row, second column.
- [XMATCH](https://spreadsheetpractice.com/functions/xmatch): XMATCH is the modern MATCH: it returns the position of a value in a list, but it defaults to an exact match, can search from the bottom up, and handles wildcards and next-larger or next-smaller matches without sorted data.
- [XLOOKUP](https://spreadsheetpractice.com/functions/xlookup): XLOOKUP searches one column or row for a value and returns the matching item from another.
- [VLOOKUP](https://spreadsheetpractice.com/functions/vlookup): VLOOKUP looks for a value in the first column of a table and returns a value from another column in the same row.
- Comparison: [XLOOKUP vs INDEX and MATCH](https://spreadsheetpractice.com/functions/compare/xlookup-vs-index-match)

## Questions

### How do INDEX and MATCH work together?

MATCH finds the row number of the value, and INDEX returns the item in that row from another column: =INDEX(C2:C5,MATCH(E2,A2:A5,0)).

### Is MATCH case-sensitive?

No. "stapler" matches "Stapler". For a case-sensitive match, use =MATCH(TRUE,EXACT(A2:A5,E2),0).

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

XMATCH defaults to an exact match and can search from the bottom up. MATCH defaults to an approximate match and works in every version.

## Practice

A graded MATCH exercise ("Find the position") is on [the MATCH page](https://spreadsheetpractice.com/functions/match). It is checked instantly, then re-checked on changed data.

Lessons that use MATCH:

- [Lesson 14: INDEX & MATCH](https://spreadsheetpractice.com/learn/index-match) (Pro)
- [Lesson 16: Lookups with several criteria](https://spreadsheetpractice.com/learn/multi-criteria-lookups) (Pro)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
