AdvancedData Analysis
Name the second-best rep with LARGE and INDEX
Combine ranking with a lookup to return a name, not just a number.
LARGE · INDEX · MATCH
14 min40 points
Find a value with INDEX and MATCH
The situation
Harbor Supply's rep commission table gets columns reordered every quarter, and the last hard-coded VLOOKUP broke silently. You want something that survives.
A1
fx
| Row number | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Row | Rep | Commission rate | Rep | Rate | |||
| 2 | 1 | Alan Reyes | Marcus Bell | 4.0% | ||||
| 3 | 2 | Dana Whitfield | Dana Whitfield | 5.5% | ||||
| 4 | Alan Reyes | 3.5% | ||||||
| 5 | Priya Raman | 6.0% | ||||||
| 6 |
Functions in this exercise
Who this is for
Recommended next
Related exercises
- IntermediateLookup & Reference
Look up a customer's region with XLOOKUP
Match a customer ID against a reference table to return their region.
XLOOKUP
8 min25 points - IntermediateLookup & Reference
Look up a unit price with VLOOKUP
Use VLOOKUP with exact match to pull prices from a product table.
VLOOKUP
8 min25 points - AdvancedData Analysis
Name the second-best rep with LARGE and INDEX
Combine ranking with a lookup to return a name, not just a number.
LARGE · INDEX · MATCH
14 min40 points