MATCH
Finds the position of a value in a list: the place of a product in a catalog, the rank of an employee in a leaderboard.
MATCH(lookup_value, lookup_array, [match_type])- [ ]optional part
- lookup_value
- What to look for: a cell or a value, for example a last name in D2.
- lookup_array
- One column or one row to search.
- match_type
- 0 means exact match. Without it, the function does an approximate match and expects a list sorted in ascending order.Optional
Return value
The position where the value was found, counting from 1 at the start of the range. If the value isn't found, you get #N/A.
Examples
A sales rep's place in the ranking
INDEX and MATCH: department by last name
Details
MATCH looks for a value in one column or one row and returns its position. The position is counted from the start of the range: if the value is in the first cell of the range, the answer is 1.
The last argument is almost always 0: that makes the function look for an exact match. If the value isn't there, it returns #N/A.
MATCH is most often paired with INDEX: MATCH finds the number of the row with the key, and INDEX takes the value from that row in the column you need. This way you can even look to the left of the key.
Common mistakes
- The third argument is left out. Then the match is approximate, and on an unsorted list the function silently returns the wrong position.
- Several columns are selected as the range. MATCH works only with one column or one row.
- With INDEX, the two ranges start on different rows. The row number is right, but the value comes from the neighboring row.