Index a Specific Rank

[tcrapo]

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.) -->

AAA	10	3rd	EEE
BBB	20	2nd	DDD
CCC	60	1st	CCC
DDD	50
EEE	40
FFF	30

<tbody>
</tbody>

 

[Andrew Poulsom]

Formula in D1 copied down:

Excel 2010
	A	B	C	D
1	AAA	10	3	EEE
2	BBB	20	2	DDD
3	CCC	60	1	CCC
4	DDD	50
5	EEE	40
6	FFF	30
Sheet1

Cell Formulas
Range	Formula
D1	=INDEX(A$1:A$6,MATCH(LARGE(B$1:B$6,C1),B$1:B$6,FALSE))

 

[tcrapo]

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.) -->

AAA	10	3rd	#VALUE!
BBB	20	2nd	#VALUE!
CCC	60	1st	#VALUE!
DDD	50
EEE	40
FFF	30

<tbody>
</tbody>

Formula in D1 copied down:

Excel 2010

	A	B	C	D
1	AAA	10	3	EEE
2	BBB	20	2	DDD
3	CCC	60	1	CCC
4	DDD	50
5	EEE	40
6	FFF	30

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D1 =INDEX(A$1:A$6,MATCH(LARGE(B$1:B$6,C1),B$1:B$6,FALSE)) <tbody> </tbody>

<tbody>
</tbody>

 

[Andrew Poulsom]

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

 

[tcrapo]

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.

 

[Andrew Poulsom]

Thank you for being a night owl like me!

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

 

[tcrapo]

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.) -->

Name	Score	Place	Name
AAA	10	3	EEE
BBB	20	2	DDD
CCC	60	1	CCC
DDD	50
EEE	40
FFF	30
123456	123456
123456	123456
123456	123456
GGG	55
HHH	45
III	35
JJJ	25
KKK	15

<tbody>
</tbody>

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

 

[Andrew Poulsom]

Like this?

Excel 2010
	A	B	C	D
1	Name	Score	Place	Name
2	AAA	10	3	DDD
3	BBB	20	2	GGG
4	CCC	60	1	CCC
5	DDD	50
6	EEE	40
7	FFF	30
8
9	123456	123456
10	123456	123456
11	123456	123456
12
13	GGG	55
14	HHH	45
15	III	35
16	JJJ	25
17	KKK	15
Sheet2

Cell Formulas
Range	Formula
D2	=INDEX(A$2:A$17,MATCH(LARGE((B$2:B$7,B$13:B$17),C2),B$2:B$17,FALSE))

 

[tcrapo]

You're the best!

Thanks,
-Tony

Like this?

Excel 2010

	A	B	C	D
1	Name	Score	Place	Name
2	AAA	10	3	DDD
3	BBB	20	2	GGG
4	CCC	60	1	CCC
5	DDD	50
6	EEE	40
7	FFF	30
8
9	123456	123456
10	123456	123456
11	123456	123456
12
13	GGG	55
14	HHH	45
15	III	35
16	JJJ	25
17	KKK	15

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

Sheet2​

Worksheet Formulas Cell Formula 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>