Problem of using INDEX/MATCH function with duplicated values

**offshore-safety** · 11-20-2010, 01:34 AM

Hi everyone,

Here is one of the challenges I faced now.

Cluster ID Score
Cluster 1 6
Cluster 2 6
Cluster 3 5
Cluster 4 3

I have a dataset with 2 columns "Cluster ID" and "Score". I want Excel to return the Cluster ID with the highest Score. I used the function:

=INDEX(A2:A5,MATCH(MAX(B2:B5),B2:B5,0))

This function works well only when there is no duplicated highest scores in the spreadsheet.

In this example, Excel only return Cluster 1 to me. What I want is returning both "Cluster 1 and 2" if there are a equal highest scores.

How can I ask Excel to do this for me?

Thanks a lot.

**grizzly6969** · 11-20-2010, 03:12 AM

you could try this -- it will rank them

**ratcat** · 11-20-2010, 03:47 AM

Here's my two cents worth

**offshore-safety** · 11-20-2010, 05:45 AM

Hi,

Thanks both of you. I am a bit more interested on ratcat's function. How can I rearrange your formula when the format is like the attached file?

This format is the actual format I am dealing with now - the row is "score" and the column is "cluster ID".

I want Excel to return the cluster ID(s) with the highest score(s)

Again, thanks for both grizzly6969 and ratcat~ you guys are so cool!