Who Sold the Most? INDEX, MATCH and MAX
Your task: In E2:E4, show the name of the top seller, the runner-up and the lowest seller.
Full instructions, scenario and goal
Scenario
A sales director keeps the results of eight salespeople in a simple table. For the quarterly bulletin she wants the name of the top seller, the name of the runner-up, and the name of the person who needs the most support, and the bulletin must update when new figures arrive.
Your task
- In E2, show the name of the salesperson with the highest sales.
- In E3, show the name of the salesperson with the second-highest sales.
- In E4, show the name of the salesperson with the lowest sales.
- Use the table in rows 2 to 9, then select Check Answer.
You are done when
- Each cell returns a name, not a sales figure.
- The names are found from the sales figures, so they change when the figures change.
What you will practise
Find the largest, second-largest or smallest value, then use MATCH and INDEX to return the label that belongs to it.
The idea in 30 seconds
MAX, LARGE and MIN tell you which number is the highest, second highest or lowest, but not who it belongs to. To get the name, find the position of that number in the list with MATCH (with 0 as the last argument for an exact match), and hand that position to INDEX, which returns the item at that position of another range. The two ranges must line up row for row.
Spreadsheet
Loading spreadsheet…
Keyboard shortcuts
- Arrow keys
- Move between cells
- Shift + Arrow keys, or drag with the mouse
- Select a range
- Ctrl + Arrow keys
- Jump to the edge of a block of data
- Type, or F2, or double-click
- Edit a cell (F2 switches arrow keys between moving and editing)
- Click or arrow keys inside a formula
- Insert a cell reference after =, +, (, comma and similar
- F4
- Cycle a reference through A1, $A$1, A$1, $A1
- Enter / Tab while editing
- Confirm and move down / right (Shift reverses)
- Esc
- Cancel the edit
- Ctrl + C / X / V
- Copy, cut and paste (formulas adjust like in Excel)
- Delete
- Clear the selected cells
- Ctrl + Z / Ctrl + Y
- Undo and redo
- Ctrl + Enter
- Check your answer
- Tab when not editing
- Leave the spreadsheet
Type your answer in cell E2. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.
New here? How an exercise works
- Read the task. Highlighted cells are yours to fill in; the rest are locked.
- Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
- Press Check answer. Stuck? Reveal hints one at a time.
The full explanation: the idea behind this exercise
MAX, LARGE and MIN tell you which number is the highest, second highest or lowest, but not who it belongs to. To get the name, find the position of that number in the list with MATCH (with 0 as the last argument for an exact match), and hand that position to INDEX, which returns the item at that position of another range. The two ranges must line up row for row.
Because INDEX reads from any range, the name can sit to the left of the numbers, something VLOOKUP cannot do. The whole answer is a chain: the value, then its position, then the label. If two people tie for the highest value MATCH returns the first one, so real reports often need a tie-break rule; the data in this exercise has no ties.
Skills you will practise
- INDEX
- MATCH
- MAX
- LARGE
- MIN
- Lookup left
Topic guide: Excel Lookup Functions Practice: VLOOKUP, XLOOKUP, INDEX MATCH
Complete first: Look Up a Shipping Rate with INDEX and MATCH
Common mistakes to avoid
- Returning the number instead of the name
- MAX alone returns 61,300. Wrap it in MATCH and INDEX to get the name.
- Forgetting the exact-match 0
- Without 0, MATCH assumes sorted data and can return a wrong position for an unsorted list.