The account at rank N (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
The board pack lists the top accounts by revenue. Revenue is in B2:B6 with names in A2:A6, and the rank to show is E1. Put that account's name in the Account cell E2.
The rank must come from E1 — the same formula should answer for rank 1 and rank 4.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | D | E | |
|---|---|---|---|---|
| 1 | Account | Revenue | Rank | 2 |
| 2 | Acme | 5000 | Account | |
| 3 | Bolt | 3200 | ||
| 4 | Cobalt | 1800 | ||
| 5 | Dana | 900 | ||
| 6 | Echo | 4100 |
Notes
LARGE(range, n) returns the nth biggest value, and MATCH finds where that value sits so INDEX can return the name from a different column. Sorting the sheet by hand would work once and break the next time it is refreshed.
How to approach
Your answer goes in E2.
- Target cell is E2
- LARGE gives you the revenue at that rank
- MATCH locates that revenue in B2:B6
- Shape: =INDEX(?, MATCH(LARGE(?, ?), ?, 0))
Function guide
FAQ
How do I get the name of the top account?
=INDEX(names, MATCH(LARGE(revenues, 1), revenues, 0)). For the nth account, replace 1 with n.
Why does MATCH return the wrong row when two accounts tie?
MATCH stops at the first equal value, so a tie returns whichever appears first. Add a tiebreaker column or use a ranking with COUNTIF if the order matters.