Return value of Column A for nth highest value in Column B

[ricdik]

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. If I had a formula for the 2d largest I can figure out the other nth Largest values I need.




[TABLE="width: 268"]
<colgroup><col><col></colgroup><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

 

[Rick Rothstein]

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. If I had a formula for the 2d largest I can figure out the other nth Largest values I need.




[TABLE="width: 268"]
<colgroup><col><col></colgroup><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

Does this formula (copied down) do what you want...

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

 

[ricdik]

Thanks for the formula, but the 2nd and 3rd largest mimic the output of the largest.

 

[ricdik]

Does this formula (copied down) do what you want...

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

It works, but if there's a duplicate like the 2nd largest it just repeats the first of the duplicates.