MATCH function
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.
- Available in
- Every current version
- Category
- Lookup functions
- 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.
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 below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| 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 |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Product | Price | Stock | Find | Result | |
| Notebook | $4.50 | 320 | Stapler | 3 | |
| Pen pack | $6.00 | 540 | 85 | ||
| Stapler | $14.99 | 85 | 2 | ||
| Binder | $7.25 | 210 |
Practice MATCH
From lesson 11, INDEX & MATCH. Graded instantly, then re-checked on changed data.
Find the position
H5, return the position of the zone from H2 within A2:A6.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)).
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.
Lessons that use MATCH
- Lesson 11: INDEX & MATCH (Pro)
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.