INDEX/MATCH with MAX: who is top?
Find the highest figure, then the name beside it.
Task:
MAX tells you the best month's sales but not which rep made them. In B9 give the name of the rep with the highest sales, and in B10 the name of the rep with the lowest.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of INDEX MATCH
0/4The same formula in the other shapes it takes at work.
- INDEX-MATCH Combination
Use INDEX and MATCH functions together for flexible lookups.
CoreAdvanced - INDEX/MATCH to the left
Return a column that sits before the one you search — which VLOOKUP cannot do.
Advanced - INDEX/MATCH with MAX: who is top?
Find the highest figure, then the name beside it.
Advanced - INDEX/MATCH with an approximate MATCH
MATCH type 1 finds the band; INDEX returns its label from any column.
Advanced
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | |
|---|---|---|
| 1 | Rep | Sales |
| 2 | Lucia | 48200 |
| 3 | Marek | 52900 |
| 4 | Nkechi | 61350 |
| 5 | Oscar | 39800 |
| 6 | Petra | 57100 |
| 7 | ||
| 8 | ||
| 9 | Top rep | |
| 10 | Bottom rep |
What this exercise teachesMay contain the answer
The value you look up does not have to be typed or come from a cell — here it is calculated. If two reps tie for top, MATCH returns the first; that is worth knowing before a leaderboard goes on a wall.