Using Index/Match to return cell value - Need to identify duplicate values

[ricdik]

[h=2][/h]

I've searched and found some things that are similar, but not exactly what I want (i.e. Index/Match). I want to look up the 1st largest value and return what is in column A. I'm then repeating for the 2nd, 3rd, etc.

Here is the formula I have, but the output for the largest and 2nd largest are the same. I need the formula to be able to identify each uniquely.

=INDEX(A$1:A$14,MATCH(LARGE(B$1:B$14,ROWS($1:1)),B$1:B$14,0))


[TABLE="class: cms_table, width: 268"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]word 1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]word 2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]word 3[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]word 4[/TD]
[TD="align: right"]46[/TD]
[/TR]
[TR]
[TD]word 5[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]word 6[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]word 7[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]word 8[/TD]
[TD="align: right"]46[/TD]
[/TR]
[TR]
[TD]word 9[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]word 10[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]word 11[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]word 12[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]word 13[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]word 14[/TD]
[TD="align: right"]18[/TD]
[/TR]
</tbody>[/TABLE]


Largest word 4
2nd Largest word 8
3rd Largest word 14​

 

[ricdik]

I knew this was tough, but thought there might be a solution.