MATCH - Find a key's relative position
Locate an order in a queue or a department in a list, then use the position in a calculation or INDEX retrieval.
By the end, you can
- Return a relative position, not a matched value.
- Use 0 for exact matching.
- Understand the sort rules for approximate match types.
Before you start: Read a one-dimensional range. INDEX retrieves a value using the position.
In this lesson
Where is the requested item?
MATCH returns where a key occurs within a single row or column. A dispatch coordinator can find an order's queue position and estimate the wait from earlier orders. The result starts at 1 inside the supplied range; it is not necessarily the worksheet row number and is not the order's text.
Syntax and match types
=MATCH(lookup_value, lookup_array, [match_type])In real Excel, omitting match_type means 1, an approximate mode. Use an explicit 0 for an unsorted identifier list.
| Argument | Purpose | Example |
|---|---|---|
lookup_valueRequired | The key or value whose position you need. | C2 |
lookup_arrayRequired | A single row or column of candidates. | A2:A6 |
match_typeOptional | 0 exact; 1 (default) largest value <= key with ascending order; -1 smallest value >= key with descending order. | 0 |
A dispatch queue position
3| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Queued order | Requested | Position | Wait minutes | Minutes per earlier order | |
| 2 | O205 | O203 | 3 | 30 | 15 | |
| 3 | O201 | |||||
| 4 | O203 | |||||
| 5 | O208 | |||||
| 6 | O202 |
Calculate from a position and handle absence
=MAX(0,(MATCH(C2,A2:A6,0)-1)*F2)Subtract one before multiplying because the first order has no earlier orders.
=MATCH("O205",A2:A6,0)The first position is 1, not zero.
=IFERROR(MATCH("O999",A2:A6,0),"Not queued")No exact match produces #N/A; the chosen guard converts that to a label.
A sorted numeric band list
0| A | B | |
|---|---|---|
| 1 | Minimum order INR | Band |
| 2 | 0 | Basic |
| 3 | 5000 | Silver |
| 4 | 10000 | Gold |
=MATCH(7500,A2:A4,1)The greatest boundary <=7,500 is 5,000, at position 2. MATCH returns 2 rather than Silver or 5,000.
For match_type -1, sort the candidates descending and select the smallest candidate not below the request. Wrong sort order can produce an incorrect approximate answer; an exact 0 match has no sorting requirement.
Limits, mistakes, and workspace differences
- An exact match generally returns the first matching position. Duplicate keys do not produce every match.
- Exact text matching ignores case and supports * or ? wildcard patterns, with ~ to escape literal wildcard characters.
- Leading/trailing spaces or number-versus-text keys can cause unexpected missing matches. Match within a single row or column.
- MATCH is available in older and current Excel; XMATCH offers an exact default and explicit reverse search in supported newer versions.
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.