AdvancedLookup & Reference
Lock ranges so a lookup survives fill-down
Use absolute references so a lookup does not break when copied down a column.
XLOOKUP
12 min40 points
Name the third-fastest supplier
The situation
Procurement wants a second and third choice on the pallet contract, not just the winner. The lead times are in one column and the supplier names in another, and what is needed is the name of whoever comes third on speed.
A1
fx
| Row number | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Supplier | Lead time | ||||
| 2 | Ridgeline Pallet | 12.00 | Third fastest | |||
| 3 | Cortez Timber | 5.00 | ||||
| 4 | Bell Harbor Supply | 9.00 | ||||
| 5 | Two Rivers Crating | 21.00 | ||||
| 6 | Kestrel Industrial | 7.00 | ||||
| 7 | Anders Woodworks | 15.00 | ||||
| 8 | Palmetto Pallet Co | 3.00 | ||||
| 9 |
Functions in this exercise
Who this is for
Recommended next
Related exercises
- 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 - IntermediateLookup & Reference
Find a value with INDEX and MATCH
Combine INDEX and MATCH to look up a value without a fixed column number.
INDEX · MATCH
10 min25 points - BeginnerStatistical Functions
Find the second-lowest quote with SMALL
Pick out the second-cheapest supplier quote rather than simply the cheapest.
SMALL
4 min10 points