Help with INDEX/MATCH

[Pete]

I'm trying to make a top 10 but I'm having problems with non unique
values.

Raw data :

Pink 1
Blue 5
Yellow 3
White 6
Purple 4
Brown 2
Red 15
Orange 48
Black 18
Green 20
Beige 22
Violet 56
Gold 12

To get my top 10 I use this, which works fine.

=LARGE(totals,1) - through to 10

I then use this to match the text title to the value.
=INDEX($B$5:$C$17,MATCH(B23,$C$5:$C$17,0),1)

Result :
1st 56 Violet
2nd 48 Orange
3rd 22 Beige
4th 20 Green

This all works fine as long as the values are unique, however if I
change Gold to 56 I get the following result

1st 56 Violet
2nd 56 Violet
3rd 48 Orange
4th 22 Beige

Any ideas on how to make this work correctly?

Many thanks
Pete

 

[Bernard Liengme]

My worksheet looks like this
A B C D E F
Pink 1 13 56 1 Violet
Blue 5 9 56 2 Gold
Yellow 3 11 48 3 Orange
White 36 4 36 4 White
Purple 4 10Brown 2 12
Red 15 8Orange 48 3
Black 18 7Green 20 6
Beige 22 5Violet 56 1
Gold 56 2In C1 I have =RANK(B5,$B$5:$B$17)+COUNTIF(B$5:B5,B5)-1
This is copied down to C17
This ranks the B values allowing for dups (thanks to Chip Pearson)
In D I have same LARGE formula as you have
In E1:E4 I have numbers 1,2,3,4
In F1 I have =INDEX($A$5:$A$17,MATCH(E5,$C$5:$C$17,0))
This is copied down to F4 and gives the required result
best wishes

 

[Bernard Liengme]

I do wish there was some way in this group to actual show how a worksheet
was laid out.
I did a copy and paste. It looked great in my message. Now it is almost
incompressible.
Pete, email me directly (get email from my website) if you wish.