Dynamic Top-N List

Excel Formulas › Dashboards & Reporting

All versionsLARGE

Show the top performers that re-rank automatically. LARGE pulls the Nth-biggest value and INDEX/MATCH fetches its label — a live leaderboard that updates as the data changes.

Quick formula: the Nth-highest value and its name:
=INDEX(names, MATCH(LARGE(values, n), values, 0))
LARGE gets the Nth-largest value; MATCH finds its row; INDEX returns the matching name.

Functions used (tap for the full reference guide):

LARGEINDEX

The example

Top 3 reps by sales.

AB
1RankName · Value
21Maria · $92k
32 / 3Sam · $88k …

The formula

The formula:

=INDEX(names, MATCH(LARGE(values, n), values, 0)) // Nth value → its name

How it works

How it works:

  1. LARGE(values, n) returns the Nth-largest value (n = 1 is the max).
  2. MATCH(…, values, 0) finds that value’s row, and INDEX(names, …) its label.
  3. Fill n = 1, 2, 3… down for a ranked list that re-sorts as data changes.
  4. Use SMALL instead of LARGE for a bottom-N list.

Break ties so names don’t repeat. If two values are equal, MATCH returns the same row for both, duplicating a name. Add a tiny tiebreaker to the values — value + ROW()/1e6 — before LARGE, or use a helper rank column. On Excel 365, SORTBY/TAKE handle ties cleanly in one spill.

Try it: interactive demo

Live demo

Values (comma-separated); shows top 3.

Variations

Bottom N

Smallest:

=INDEX(names, MATCH(SMALL(values, n), values, 0))

Tie-break values

Unique each:

=LARGE(values + ROW(values)/1E6, n)

365 spill

Sorted top N:

=TAKE(SORTBY(names, values, -1), n)

Pitfalls & errors

Ties duplicate names. Add a tiebreaker before LARGE or use a rank helper.

n is the rank. LARGE(...,1) is the maximum, not the minimum.

Numbers only. LARGE ignores text — keep the value range numeric.

Practice workbook

📊
Download the free Dynamic Top-N List practice workbook
A top-N sheet with the bottom-N, tie-break, and 365-spill variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I make a dynamic top-N list in Excel?
Use =INDEX(names, MATCH(LARGE(values, n), values, 0)) and fill n = 1, 2, 3… for a self-ranking leaderboard.
How do I avoid duplicate names from ties?
Add a tiny tiebreaker to values before LARGE: =LARGE(values + ROW(values)/1E6, n), or use SORTBY/TAKE on 365.
How do I get the bottom N instead?
Swap LARGE for SMALL: =INDEX(names, MATCH(SMALL(values, n), values, 0)).

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Nth largest value · Sum top N · SORTBY helper

Function references: LARGEINDEX