Find the Top 3 (or Second Highest) With LARGE and SMALL

The Excel LARGE function finds the second-highest value, the third-highest, or any position you choose, and SMALL does the same from the bottom. MAX and MIN only give you the very top and bottom. "Find the second-highest sale" and "total the top 3" are favourite interview questions, and LARGE answers both in one line.

What does the Excel LARGE function do?#

LARGE returns the k-th largest number in a range. The letter k is just the position you want: 1 for the largest, 2 for the second largest, and so on.

=LARGE(array, k)
=SMALL(array, k)

• array is the range of numbers, such as B2:B9.
• k is the position, counted from the top for LARGE and from the bottom for SMALL.

The parts inside the parentheses are called arguments, and they are separated by commas. The order matters: range first, then k. =LARGE(2,B2:B9) doesn't work.

Find the second-highest value#

Use =LARGE(range,2). Here are a sales team's Q3 results:

LARGE(B2:B9,1) is the same as MAX(B2:B9), and SMALL(B2:B9,1) is the same as MIN(B2:B9). If those two are new to you, start with AVERAGE, MIN and MAX in Excel.

You don't need to sort the data. LARGE and SMALL rank the numbers internally and leave the sheet as it is.

How to sum the top 3 values#

Add three LARGE results, one for each position:

=LARGE(B2:B9,1)+LARGE(B2:B9,2)+LARGE(B2:B9,3)

That's 72,300 + 66,800 + 61,750 = 200,850.

A shorter version gives the same result:

=SUM(LARGE(B2:B9,{1,2,3}))

The curly brackets {1,2,3} are an array constant: a list of values typed straight into the formula. LARGE returns three results, and SUM adds them. Swap SUM for AVERAGE to get the average of the top 3: 66,950. Averages of other data often have long decimals, so you may want to wrap the formula in ROUND, as covered in how to round numbers in Excel.

List the top 3 in separate cells#

To show a leaderboard, put the positions 1, 2 and 3 in their own cells and point k at them. With 1, 2 and 3 in D2:D4, type this in E2 and fill it down to E4:

=LARGE($B$2:$B$9,D2)

E2 shows 72,300, E3 shows 66,800 and E4 shows 61,750. The $ signs lock the sales range so it doesn't slide down as you fill, while D2 moves to D3 and D4. If the $ signs are new to you, read absolute references and the $ sign. Change D4 to 5 and E4 updates to the fifth-highest sale.

How does LARGE handle ties?#

LARGE counts every value, including repeats. It does not skip to the next different number.

Say Fay's sale was entered wrong and was really 72,300, the same as Dev's. Now:

• =LARGE(B2:B9,1) returns 72,300 (Dev).
• =LARGE(B2:B9,2) returns 72,300 too (Fay).
• The top-3 total becomes 72,300 + 72,300 + 61,750 = 206,350.

This is usually what you want: two people both earned the top amount. If you need the second-highest different value, you'd first remove repeats, for example with the UNIQUE function in Microsoft 365: =LARGE(UNIQUE(B2:B9),2).

How to get the name, not just the number#

LARGE returns a number, not the row it came from. To show who made the second-highest sale, look up the result in the sales column and return the name:

=XLOOKUP(LARGE(B2:B9,2),B2:B9,A2:A9)

This returns Fay. XLOOKUP needs Excel 2021 or Microsoft 365. In older Excel, use INDEX and MATCH:

=INDEX(A2:A9,MATCH(LARGE(B2:B9,2),B2:B9,0))

One warning: with ties, both formulas find the first matching row each time, so the same name can appear twice on a leaderboard. Learn more in XLOOKUP for beginners and INDEX MATCH explained. If you want the whole list in order, SORT and SORTBY can sort the table with a formula.

Top values with a condition#

LARGE ranks the whole range. To rank only some rows, such as one region, filter the numbers first. If the regions are in C2:C9, this returns the second-highest sale in the North region:

=LARGE(FILTER(B2:B9,C2:C9="North"),2)

FILTER needs Excel 2021 or Microsoft 365; see the FILTER function. For just the single highest or lowest value with a condition, MAXIFS and MINIFS are simpler, as shown in MAXIFS and MINIFS.

Why does LARGE return #NUM!?#

LARGE returns #NUM! when k doesn't fit the data. The table above has eight numbers, so k must be between 1 and 8.

• =LARGE(B2:B9,9) returns #NUM!: there is no ninth-largest value.
• k of 0 or a negative number also returns #NUM!.
• Blank cells and text are skipped, so they don't count towards the total. If two reps had no sales entered, only six numbers would be left, and k = 7 would fail.

Before you build a "top 10" list, check how many numbers you have with =COUNT(B2:B9). COUNT, COUNTA and COUNTBLANK shows how.

Where LARGE and SMALL are useful at work#

LARGE and SMALL turn up wherever people rank things. Common uses:

• A sales leaderboard: top 3 reps and their totals.
• A shortlist: the second-best candidate score, in case the first declines.
• Cost control: the three largest expenses this month.
• Supplier checks: the second-lowest quote, when the cheapest is not available.

Once you have the top values, the next question is often how they changed. The percent change formula shows how much the top rep grew compared with last quarter.

Practise it#

Practise LARGE and SMALL in a live spreadsheet: you'll build the leaderboard above, see what happens when two sales tie, total the top 3 and find the second-lowest sale. Then try the runner-up exercise on this page.