Knowledge base / Excel

Excel LARGE: the second-largest value and ties

Select the largest observations, keep repeated values in their positions, and distinguish a numeric result from a particular record when scores tie.

Kajo RudzińskiPublished Updated 4 min read

Five towers on a ranking shelf begin with two equally tall towers

When I need the second- or third-largest value, I use LARGE. Its Polish name is MAX.K, confirmed by Microsoft’s Polish documentation. The older Polish version of this entry used an incorrect function name. The existing addresses stay in place while the content and formulas are corrected.

The first question is whether you need the next observation in order or the next different number. Those answers can diverge when values repeat. I establish the distinction before using the output in a ranking of people, products or records.

Syntax and Excel version

The syntax is LARGE(array,k), or MAX.K(tablica;k) in Polish. Here, k is a positive integer: 1 for the largest observation, 2 for the second, 3 for the third. It is neither a worksheet row number nor a record identifier.

Microsoft documents the function for Excel Microsoft 365, 2024, 2021, 2019 and 2016. These examples use ordinary numeric cell ranges and comma separators. Regional settings determine the separator in your workbook. Every identifier and value below is synthetic.

Five records, including a tie

Enter this table in A1:B6. Column A contains identifiers and column B scores. Put output formulas outside the source, for example in D2:D4, so creating the result list does not overwrite the data being analyzed.

A: ID B: Score
A01 100
A02 600
A03 450
A04 600
A05 300
=LARGE($B$2:$B$6,2)

The expected result is 600. Mentally order the observations descending: 600, 600, 450, 300, 100. The second position still contains 600. LARGE does not remove repeats before selecting the position.

Return the top three observations

Enter the expression for k equal to 1 in D2, 2 in D3 and 3 in D4. Dollar signs keep the source range fixed. Each expression is ready to paste without an inline comment that Excel would try to interpret as formula text.

=LARGE($B$2:$B$6,1)
=LARGE($B$2:$B$6,2)
=LARGE($B$2:$B$6,3)

The results are 600, 600 and 450. In Polish Excel, the second expression is =MAX.K($B$2:$B$6;2). The source does not have to be sorted for the function to return a value. However, the result does not carry the corresponding record or its identifier along with it.

If the question asks for the second distinct value, the answer here is 450. That requires defining a distinct-value set first. Do not remove duplicates from the original data without considering their meaning: two people with equal scores can still be two legitimate observations.

A lens examines two matching tall towers while separate record markers remain distinct

Check k boundaries and errors

Microsoft’s LARGE documentation specifies #NUM! for an empty array, k at or below zero, or k exceeding the number of data points. Polish Excel calls that error #LICZBA!. A sixth position does not exist among the five numeric scores in this table.

Here, k equal to 5 returns 100, the smallest of the five observations. Values 0 and 6 produce errors. Those are useful boundary cases before a report lets a reader choose the requested position.

I avoid automatically wrapping the formula in IFERROR(...,"Not enough data"). IFERROR catches different errors, so that label may assign the wrong cause. Check k, the range and source errors first, then choose an accurate message.

A value is different from a record identifier

Suppose you want the ID belonging to the second-largest observation. This INDEX and MATCH combination exposes an important limit. Use the same A1:B6 source and place the result in F2.

=INDEX(A2:A6,MATCH(LARGE(B2:B6,2),B2:B6,0))

LARGE returns 600. MATCH with argument 0 finds the first exact match, as its documentation explains. INDEX therefore returns A02, rather than automatically choosing A04 as a second-ranked person. Using k equal to 1 and 2 can return the same ID twice.

If the ranking needs unique records, define a tie rule, such as score descending followed by ID ascending. That is an ordering of complete records. LARGE supplies a numeric value or threshold; it does not itself implement a policy for returning every tied name or choosing between tied records.

Try the distinction yourself

Change A04’s score from 600 to 590. Predict the top three values: 600, 590, 450. Restore the tie, then describe the difference between “second observation,” “second distinct value” and “all records with the highest score.” Each phrase states a different requirement.

For the smallest observations, continue with SMALL. For a largest-value report, begin with a clear tie policy and expected row count. That keeps the final list from becoming an accidental consequence of source order.

Key takeaways

What is LARGE called in Polish Excel?

MAX.K. The old Polish entry used the incorrect name DUŻA. The function takes a range and a position k counted from the largest value.

Does LARGE remove repeated numbers?

No. Two occurrences of 600 occupy two positions. The second-largest observation can equal the first-largest value.

Does LARGE identify a particular person or record?

No. It returns a value. Selecting records when values tie needs a separate rule; a basic exact lookup may return only the first match.

AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.

Kajo Rudziński

Newsletter KajoData

Let’s keep in touch.

I write about data analysis, learning and changing careers. My newsletter and the sign-up form are in Polish. If that works for you, I’d be glad to see you there.