The vendor at rank N by spend (LARGE + INDEX/MATCH)
Test case
1
Reset
Submit
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
○ Ready Enter formula in highlighted cells, then click Submit.
Test case
1
Reset
Submit
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
○ Ready Enter formula in highlighted cells, then click Submit.
The problem
Problem statement
Spend per vendor is in B2:B6 with names in A2:A6. The rank to report is E1. Put that vendor's name in the Vendor cell E2.
The rank must come from E1, so the same formula answers for the top vendor and the fifth.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | D | E | |
|---|---|---|---|---|
| 1 | Vendor | Spend | Rank | 2 |
| 2 | Acme | 42000 | Vendor | |
| 3 | Bolt | 18000 | ||
| 4 | Cobalt | 26000 | ||
| 5 | Dana | 9000 | ||
| 6 | Echo | 31000 |
Notes
LARGE gives the nth biggest number, MATCH finds where it sits, and INDEX returns the name from another column. Sorting the table by hand would work once and then break on the next refresh.
How to approach
Your answer goes in E2.
- Target cell is E2
- LARGE gets the spend at that rank
- MATCH locates it in B2:B6
- Shape: =INDEX(?, MATCH(LARGE(?, ?), ?, 0))
Function guide
FAQ
How do I find the top vendor by spend?
=INDEX(vendors, MATCH(LARGE(spend, 1), spend, 0)). Change 1 to n for another rank.
Why does a tie give the wrong vendor?
MATCH stops at the first equal value. With ties, sort or add a tiebreaker column; the formula cannot guess which of two equal vendors you meant.