Excel track · Exercise 44 of 86 Paid
MATCH: find a value's position
Find where a value sits in a list, counting the first cell as 1.
Intermediate
Four months and their units
Read-only data · answer in B7
In B7, find the position of the month in B6 within A2:A5, counting the first row as 1.
| A | B | |
|---|---|---|
| 1 | Month | Units |
| 2 | Jan | 120 |
| 3 | Feb | 180 |
| 4 | Mar | 150 |
| 5 | Apr | 210 |
| 6 | Which month? | Mar |
| 7 | Position |
Reference answer
What it does
MATCH counts how far down a list a value sits. The months in A2:A5 run Jan, Feb, Mar, Apr, and B6 holds Mar, so MATCH returns 3. That 3 is a position inside A2:A5, which is row 4 on the sheet.
The third argument decides how much tolerance MATCH allows. A zero asks for an exact match and is the safe choice here, because Jan to Apr is calendar order and approximate search assumes ascending sort. Set 1 and MATCH returns the last entry it judges to be at or below the value.
A month that is not in the list returns #N/A. There is no position for a value the list does not hold, so anything built on MATCH fails visibly. INDEX, in the next exercise, takes this 3 and reads the third cell of another column.
Syntax
=MATCH(lookup_value, lookup_array, [match_type])Worked example
On Four months and their units, =MATCH(B6,A2:A5,0) returns 3.
=MATCH(B6,A2:A5,0)Common errors
| Error | What it means and how to fix it |
|---|---|
#N/A | No cell in A2:A5 holds the month, so MATCH has no position to return. |
#VALUE! | The searched range was a block rather than one row or column, or the match type was text. |
Wrong row | The match type was left out or set to 1, so MATCH searched a calendar-order list as though it were ascending and picked a neighbouring month. |
At work
Feeding a row number to INDEX without typing 3 into the formula by hand.