Index a Specific Rank

tcrapo

New Member
Joined
Apr 8, 2014
Messages
19
My Brain isn't working tonight.

I want to find the cell with the corresponding rank.
In the example below, I want to find the third highest ranked score in the range and place it's label in cell D1.
The result in D1 should be EEE.
In D2 I want the 2nd highest score. Result should be DDD
in D3 I want the higest score. Result should be CCC
(Desired Result in Bold)

I want to do this with an index formula with a nested ranking/match formula, but I don't know how to do it.
I'd like to do this without the MAX/MIN formulas.

Windows 7
Excel 2007
Thanks,
-Tony

<!-- Please do not remove this header --><!-- Table easily created from Excel with ASAP Utilities (ASAP Utilities for Excel - The popular add-in for Excel users. Easy to use tools that save time and speed up your work in Excel. We also offer a free edition.) -->
AAA103rdEEE
BBB202ndDDD
CCC601stCCC
DDD50
EEE40
FFF30

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Formula in D1 copied down:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">AAA</td><td style="text-align: right;;">10</td><td style="text-align: right;;">3</td><td style=";">EEE</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">BBB</td><td style="text-align: right;;">20</td><td style="text-align: right;;">2</td><td style=";">DDD</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">CCC</td><td style="text-align: right;;">60</td><td style="text-align: right;;">1</td><td style=";">CCC</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">DDD</td><td style="text-align: right;;">50</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">EEE</td><td style="text-align: right;;">40</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">FFF</td><td style="text-align: right;;">30</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D1</th><td style="text-align:left">=INDEX(<font color="Blue">A$1:A$6,MATCH(<font color="Red">LARGE(<font color="Green">B$1:B$6,C1</font>),B$1:B$6,FALSE</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

tcrapo

New Member
Joined
Apr 8, 2014
Messages
19
Hi Andrew,

Thanks for the quick reply, but I'm getting #VALUE! Errors.
What am I doing wrong?

<!-- Please do not remove this header --><!-- Table easily created from Excel with ASAP Utilities (ASAP Utilities for Excel - The popular add-in for Excel users. Easy to use tools that save time and speed up your work in Excel. We also offer a free edition.) -->
AAA103rd#VALUE!
BBB202nd#VALUE!
CCC601st#VALUE!
DDD50
EEE40
FFF30

<tbody>
</tbody>


Formula in D1 copied down:

Excel 2010
ABCD
1AAA103EEE
2BBB202DDD
3CCC601CCC
4DDD50
5EEE40
6FFF30

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
D1=INDEX(A$1:A$6,MATCH(LARGE(B$1:B$6,C1),B$1:B$6,FALSE))

<tbody>
</tbody>

<tbody>
</tbody>
 

tcrapo

New Member
Joined
Apr 8, 2014
Messages
19

ADVERTISEMENT

HAHA, I'm such an idiot...
Proof that I need sleep!

Thank you for being a night owl like me!
I greatly appreciate the help.
-Tony

You need to change 3rd, 2nd and 1st to 3, 2, 1 as I did.
 

tcrapo

New Member
Joined
Apr 8, 2014
Messages
19

ADVERTISEMENT

Follow up Question...

How could I manipulate this formula over groups of ranges?
Like in this example below
A2:A7 & A13:A17 (Without including A8:A12)


<!-- Please do not remove this header --><!-- Table easily created from Excel with ASAP Utilities (ASAP Utilities for Excel - The popular add-in for Excel users. Easy to use tools that save time and speed up your work in Excel. We also offer a free edition.) -->
NameScorePlaceName
AAA103EEE
BBB202DDD
CCC601CCC
DDD50
EEE40
FFF30
123456123456
123456123456
123456123456
GGG55
HHH45
III35
JJJ25
KKK15

<tbody>
</tbody>


It's 8:25 AM here in the UK. :)
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Like this?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Name</td><td style=";">Score</td><td style=";">Place</td><td style=";">Name</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">AAA</td><td style="text-align: right;;">10</td><td style="text-align: right;;">3</td><td style=";">DDD</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">BBB</td><td style="text-align: right;;">20</td><td style="text-align: right;;">2</td><td style=";">GGG</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">CCC</td><td style="text-align: right;;">60</td><td style="text-align: right;;">1</td><td style=";">CCC</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">DDD</td><td style="text-align: right;;">50</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">EEE</td><td style="text-align: right;;">40</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">FFF</td><td style="text-align: right;;">30</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">123456</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">123456</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">123456</td><td style="text-align: right;;">123456</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">GGG</td><td style="text-align: right;;">55</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">HHH</td><td style="text-align: right;;">45</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">III</td><td style="text-align: right;;">35</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">JJJ</td><td style="text-align: right;;">25</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">KKK</td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D2</th><td style="text-align:left">=INDEX(<font color="Blue">A$2:A$17,MATCH(<font color="Red">LARGE(<font color="Green">(<font color="Purple">B$2:B$7,B$13:B$17</font>),C2</font>),B$2:B$17,FALSE</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

tcrapo

New Member
Joined
Apr 8, 2014
Messages
19
You're the best!
:)
Thanks,
-Tony


Like this?

Excel 2010
ABCD
1NameScorePlaceName
2AAA103DDD
3BBB202GGG
4CCC601CCC
5DDD50
6EEE40
7FFF30
8
9123456123456
10123456123456
11123456123456
12
13GGG55
14HHH45
15III35
16JJJ25
17KKK15

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
D2=INDEX(A$2:A$17,MATCH(LARGE((B$2:B$7,B$13:B$17),C2),B$2:B$17,FALSE))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,108,507
Messages
5,523,308
Members
409,509
Latest member
CheekyDevil2386

This Week's Hot Topics

Top