How to find the second or third largest value with LARGE
MAX gives you the biggest. LARGE gives you the one you ask for, the second biggest, the third, and so on, for a top-three list rather than a single winner.
The formula
=LARGE(B:B,3) in column D
What each part does
LARGE
Finds a value by how big it is, counting down from the top. LARGE(range, 1) is the biggest, 2 the second biggest, and so on.
3
Which one to return, counting from the largest. Change it to 1 for the biggest, or 5 for the fifth biggest.
How it works, and what to watch for
LARGE takes the range and a position: 1 is the biggest, 2 the second, and up from there. SMALL does the same from the bottom.
Ties count as separate places, so two equal values fill the first and second spots rather than sharing one.
For a proper top-three list, fill 1, 2 and 3 down three cells, or hand LARGE a small sequence.
Questions
How do I list the top three in a row?
Use LARGE with 1, then 2, then 3, in three cells. On Microsoft 365 you can hand it {1;2;3} and let it spill.
How do I get the smallest few instead?
SMALL works exactly the same way, counting up from the bottom.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
