List top 5 without sorting

[justme]

I know you can use the Max() function to find the greatest value in a range. Is there a function to find the next highest number, then the third highest and so on?

 

[pagrender]

Here's a method that should work, if you don't mind a helper column:

If you data is in A1:A15, enter this formula in B1 and drag down:
=RANK(A1,$A$1:$A$15)+COUNTIF($A$1:A1,A1)-1

The previous formula will rank all values in the list while ensuring there are no ties.

You can then use a vlookup for 1,2,3... as you see fit.

 

[Aladin Akyurek]

I know you can use the Max() function to find the greatest value in a range. Is there a function to find the next highest number, then the third highest and so on?

Entered in C2 and copied down...

=LARGE($A$2:$A$20,ROWS($C$2:C2))

would list the numbers is A2:A20 in descending order.

 

[MrKowz]

Try using the LARGE function:

=LARGE(A:A,1)
=LARGE(A:A,2)
=LARGE(A:A,3)
=LARGE(A:A,4)
=LARGE(A:A,5)

 

[justme]

Hello all, thank you!!

The Large function is operating perfectly.

 

[Aladin Akyurek]

Hello all, thank you!!

The Large function is operating perfectly.

You are welcome. Thanks for providing us feedback.