Function guides ยท Lookup
MATCH function
Returns the position of a value within a row or column.
Syntax
=MATCH(lookup_value, lookup_array, [match_type])
| Argument | What it is |
|---|---|
lookup_value | The value to find. |
lookup_array | A single row or column to search. |
match_type | 0 for exact. 1 (default) or -1 for approximate on sorted data. |
What MATCH does
MATCH looks for a value in a single row or column and tells you where it is. The answer is a position, counting from 1.
That sounds like a small thing, and it is the missing half of INDEX. INDEX reads a value at a position. MATCH finds the position. Together they form a lookup that is more flexible than VLOOKUP.
The third argument decides how the search works. Zero means find this exact value. One means find the largest value that does not exceed it.
MATCH also answers questions about order. The position of the maximum value tells you which row is first, and MATCH on a date column with match type 1 finds the last entry on or before a given day. In a sheet that is sorted by date, that is how you pick the right price or rate that was in force on a particular date.
MATCH examples
1. Where is this name in the list
| A | B | C | |
|---|---|---|---|
| 1 | Rep | ||
| 2 | Dana | 3 | |
| 3 | Luis | ||
| 4 | Mei | ||
| 5 | Omar |
MATCH does not return the value. It returns a position: Mei is the third item in A2:A5. The 0 asks for an exact match.
2. Approximate match on sorted bands
| A | B | C | |
|---|---|---|---|
| 1 | Min score | ||
| 2 | 0 | 3 | |
| 3 | 60 | ||
| 4 | 80 | ||
| 5 | 90 |
With match type 1, MATCH returns the position of the largest value that is less than or equal to the lookup value. The bands must be sorted ascending. A score of 83 sits at position 3, the band that starts at 80.
3. What a miss looks like
| A | B | C | |
|---|---|---|---|
| 1 | Rep | ||
| 2 | Dana | #N/A | |
| 3 | Luis | ||
| 4 | Mei |
A value that is not in the list returns #N/A. That is useful to know: wrap a lookup in IFNA when a miss is a normal outcome, and let the error show when it is not.
Common MATCH mistakes
- Leaving off the match type. The default is 1, an approximate match that assumes sorted data. On unsorted data it returns a wrong position or #N/A. Type 0 unless you mean bands.
- Searching a two-dimensional range. MATCH needs a single row or column. A range such as A2:B5 returns #N/A.
- Reading the result as a value. The 3 is a position inside the lookup range, not the row number on the sheet, unless the range starts on row 1.
MATCH on Excel job tests
MATCH rarely stands alone. It is the inner half of an INDEX and MATCH lookup, or the answer to "what is the position of the highest score".
Learn the three match types. 0 is exact and the right choice for IDs and names. 1 finds the largest value not above the target and needs ascending order, which is how grade bands and tax brackets work. -1 is the mirror image for descending lists.
In a two-way lookup, two MATCH calls feed INDEX. Test by changing the lookup key; if the answer does not follow, a position got typed in by hand.
Related functions
MATCH questions
- What does MATCH return?
- The position of the lookup value in the range, counting from 1. It does not return the value itself.
- What does the 0 in MATCH mean?
- It asks for an exact match. Without it, MATCH assumes sorted data and finds the nearest value below.
- Why does MATCH return
- The value is not in the range, or it differs by a space, a type, or a case of hidden characters. Try TRIM on the lookup value.
- Is MATCH case sensitive?
- No. "mei" finds "Mei". Use EXACT in an array formula for case-sensitive matching.
Practice MATCH
Try it in a working grid: the free lesson INDEX and MATCH: look in any direction (module 16 of Analyst) has checked MATCH exercises.
Practice this function