Largest 5 Numbers in Range and their associated reference

[amoorman]

Hi,

I have a range of id's and a range of monetary values.

Col A Col B
a 100
b 10
c 20
d 20
etc etc

I would like to create a list in another worksheet that shows the n highest values (in col B) and the id of those values.

so you would see this for the top 3 values.
ID Value
a 100
c 20
d 20

I have this formula which nearly works:

to find ID (where this formula is entered into cell A2):
INDEX('[TEST.xls]TEST1'!$A$2:$A$192,MATCH(B2,'[TEST.xls]TEST1'!$B$2:$B$192,0))

to find value (where this formula is entered into cell B2):
LARGE('[TEST.xls]TEST1'!$B$2:$B$192,ROW()-ROW($B$1))

However the Index function to find the ID only finds the first ID where there is a duplicate value. e.g. in the example above it would display id c twice and not id d.

Anyone know how i can solve this?

Thanks,

 

[RonB1111]

If your data is in A2:B5, use a helper column (Col C) - enter this in C2 and copy to C5:
=RANK(B2,$B$2:$B$5)+COUNTIF($B$2:B2,B2)-1

Then use these formulas to return the ID for the highest 3:
Largest: =INDEX(A:A,MATCH(SMALL(C:C,1),C:C,0),1)
2nd: =INDEX(A:A,MATCH(SMALL(C:C,2),C:C,0),1)
3rd: =INDEX(A:A,MATCH(SMALL(C:C,3),C:C,0),1)

Use these formulas to return the value of the highest 3:
Largest: =INDEX(B:B,MATCH(SMALL(C:C,1),C:C,0),1)
2nd: =INDEX(B:B,MATCH(SMALL(C:C,2),C:C,0),1)
3rd: =INDEX(B:B,MATCH(SMALL(C:C,3),C:C,0),1)