The Excel MATCH function returns the position of a value in a row or column — not the value itself. That position becomes powerful ammunition for other functions, most famously INDEX, making "INDEX/MATCH" the most flexible lookup technique that works in every version of Excel.
0 as the third argument for an exact match — the default is approximate.
Syntax
| Argument | Description | |
|---|---|---|
lookup_value | Required | The value to find. |
lookup_array | Required | A single row or single column to search. |
match_type | Optional | 0 = exact match (use this 95% of the time) · 1 = largest value ≤ lookup_value, requires ascending sort (default!) · -1 = smallest value ≥ lookup_value, requires descending sort. |
Same trap as VLOOKUP: the default match_type is 1 (approximate). On unsorted data that returns wrong positions with no error. Type the 0 every time.
Find a position (exact match)
The roster below has names in A2:A7. To find which row Priya Patel occupies:
| A | B | D | E | ||
|---|---|---|---|---|---|
| 1 | Name | Office | Find | Position | |
| 2 | James Chen | Dallas | Priya Patel | 4 | |
| 3 | Maria Lopez | Houston | |||
| 4 | Devon Smith | Austin | |||
| 5 | Priya Patel | Denver | |||
| 6 | Alex Rivera | Dallas | |||
| 7 | Sam Tanaka | Houston |
Note the result is 4 even though she sits on worksheet row 5 — MATCH counts positions within the range you give it.
With exact match, wildcards work too:
Try it: interactive MATCH demo
Pick a value and see the position MATCH returns from the roster above — including the wildcard and the not-found case.
Approximate match: brackets and thresholds
With match_type 1 and an ascending sorted list, MATCH returns the bracket a number falls into — here, which scoring tier an 84 lands in against thresholds 0 / 60 / 70 / 80 / 90:
This pairs naturally with INDEX to return the bracket label. (match_type -1 does the reverse for descending lists, useful for "smallest value that satisfies a minimum.")
Feed MATCH into INDEX
MATCH finds where; INDEX fetches what. Return Priya’s office without knowing her row:
Use two MATCHes for a fully dynamic two-way lookup:
Modern alternative: Excel 365 adds XMATCH — same idea with exact-match default, search-from-end, and wildcards without mode switches. And XLOOKUP often replaces the combo entirely.
Errors & common pitfalls
#N/A — value not found. With match_type 0, any miss returns #N/A. Check for stray spaces and text-formatted numbers, or wrap in IFERROR/IFNA for legitimate misses.
Pitfall: the approximate default. Omitting match_type means 1, which silently returns wrong positions on unsorted data.
Pitfall: position, not worksheet row. MATCH counts within the range you give it. If your range starts at A5, a result of 1 means row 5. Feeding MATCH positions into functions that expect worksheet rows (like INDIRECT or OFFSET against whole columns) causes off-by-N bugs.
Pitfall: one row OR one column. lookup_array must be a single row or single column — a 2-D range returns #N/A.
Practice workbook
Frequently asked questions
What's the difference between MATCH and VLOOKUP?
How do I get the value instead of the position?
=INDEX(return_range, MATCH(value, lookup_range, 0)).Is MATCH case-sensitive?
What is XMATCH and should I use it?
Can MATCH work with two criteria?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class