how list highest to smallest values?

[jgomez]

Hi there I have this formula in cell c2:

=INDEX(A:A,MATCH(MAX(B:B),B:B,0))

It brings over the name of who has the highest dollar value... is there something i can add so when i drag down it list from largest to smallest? & ran into an issue with those that have same balance... don't know how we would go around that...

[Paul]

Hi Jgomez,

If you create a helper formula in another column this can be done quite easily. For example:

1. Insert a new column to the right of B (Balance).
2. In C2 use the formula:

=RANK(B2,$B$2:$B$6)+COUNTIF($B$2:B2,B2)-1

3. Fill that down to C6
4. In D2 use the formula:

=INDEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,0))

5. Fill that down to D6 and you should have your ordered list.

Hope that helps!

[shg]

if you have no duplicates, then in C2 and copy down,

=INDEX(A:A, MATCH(LARGE(B:B, ROWS(C$2:C2)), B:B, 0))

[jgomez]

Thank you both! I didn't know about the rank function...

SHG... when you drag down because there is a tie it repeats david & doesn't mention Jake...

[Paul]

The first formula is used to create unique ranks, since you have ties. The second then searches for the unique ranks in order, rather than searching the original data it is based on with ties.

Shg's is essentially the latter half of that, not accounting for the ties. It's all good, though, and glad you have it figured out.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click Edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save