MATCH: find a position before fetching a value
MATCH tells you where something occurs in a range, rather than returning the adjacent value. Knowing that position can make a larger lookup formula less mysterious.
Formula syntax
=MATCH(lookup_value, lookup_array, [match_type])
What goes into it
lookup_value
Value to find.
lookup_array
One row or one column to search.
match_type
0 exact, 1 approximate ascending, -1 approximate descending in classic MATCH.
A quick example first
=MATCH(E2,A2:A100,0)
Returns E2’s position within A2:A100 using exact match.
=INDEX(C2:C100,MATCH(E2,A2:A100,0))
Uses MATCH to choose the row returned by INDEX.
Find the row position before retrieving the price
Enter IDs in A2:A5 and corresponding amounts in B2:B5. The duplicate demonstrates which position exact MATCH selects.
| Row | A: ID | B: Amount |
|---|---|---|
| 2 | P-101 | 40 |
| 3 | P-102 | 10 |
| 4 | P-103 | 25 |
| 5 | P-102 | 12 |
| Formula | Expected result / interpretation |
|---|---|
| =MATCH(“P-102”,A2:A5,0) | 2 — position inside A2:A5, not worksheet row 2 |
| =INDEX(B2:B5,MATCH(“P-102”,A2:A5,0)) | 10 |
| =MATCH(“P-999”,A2:A5,0) | #N/A |
A result of 2 points to A3 because the search range starts at A2. The later duplicate is not selected. A separate MATCH cell can make a lookup easy to audit: verify its position, then feed that position to INDEX.
Before you copy this into a real sheet
Always supply 0 for an exact key lookup. Omitting the third argument selects approximate matching and introduces an ascending-order assumption. Sorting is unnecessary for the exact example. MATCH is not a case-sensitive ID validator; if case has business meaning, design and test a case-sensitive lookup. For last-to-first searching, consider XMATCH or XLOOKUP in a supported version instead of assuming MATCH returns the latest record.
Useful habits
- Use 0 for exact matching unless approximate matching is intentional.
- MATCH returns a position, not the matched value itself.
- XMATCH offers more flexible match and search modes in newer Excel versions.
Where people get caught out
- Expecting MATCH to return the cell contents.
- Using approximate match on unsorted data.
Other functions worth a look
A detail worth remembering
Exact matching is the safe starting point for IDs. Approximate matching has sorting assumptions that are easy to miss.
Reference and examples
The examples above use small, invented datasets and English-language formula names. Check your Excel version and argument separator against the official Excel function reference if a formula behaves differently on your computer.