Help with INDEX/MATCH

  • Thread starter Thread starter Pete
  • Start date Start date
P

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
 
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
 
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.
 
Back
Top