Need to return text from a cell based on 1st, 2nd, 3rd and 4th place ranking

[squirl1899]

Thank you in advance for helping with this, I have been searching for 3 days now and cannot seem to find the magic formula.

A1:A3, A4:6, A7:A9, A10:A12, A13:A15 & A16:A18 are merged in the actual spreadsheet.

Column F is the results I am wanting returned. I need F1, F4, F7, F10 & F13 to populate the corresponding names based on their rank of "% Goal".

What I am trying to do is rank all 6 teams based on their "% Goal" and then return the corresponding names from column A in the correct cell in column F.

[TABLE="class: grid, width: 410, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Mike
[/TD]
[TD]Goal
[/TD]
[TD="align: right"]$102,500
[/TD]
[TD][/TD]
[TD]1st
[/TD]
[TD]Sam&Frank
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]&
[/TD]
[TD="align: right"]
[LEFT]Actual[/LEFT]
[/TD]
[TD]$4,740
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Chris
[/TD]
[TD="align: right"]% Goal
[/TD]
[TD]4.62%
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Peter
[/TD]
[TD]Goal
[/TD]
[TD="align: right"]
[LEFT]$64,000[/LEFT]
[/TD]
[TD][/TD]
[TD]2nd
[/TD]
[TD]Bill&Wyndell
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]&
[/TD]
[TD="align: right"]Actual
[/TD]
[TD]$20,102
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Jennifer
[/TD]
[TD="align: right"]% Goal
[/TD]
[TD]31.41%
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Donna
[/TD]
[TD]Goal
[/TD]
[TD="align: right"]$52,500
[/TD]
[TD][/TD]
[TD]3rd
[/TD]
[TD]Peter&Jen
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]&
[/TD]
[TD="align: right"]Actual
[/TD]
[TD]$3,768
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]Alisha
[/TD]
[TD="align: right"]% Goal
[/TD]
[TD]7.18%
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]Janie
[/TD]
[TD]Goal
[/TD]
[TD="align: right"]$57,000
[/TD]
[TD][/TD]
[TD]4th
[/TD]
[TD]Janie& Shawn
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]&
[/TD]
[TD="align: right"]Actual
[/TD]
[TD]$14,899
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]Shawn
[/TD]
[TD="align: right"]% Goal
[/TD]
[TD]26.14%
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]Bill
[/TD]
[TD]Goal
[/TD]
[TD="align: right"]$49,000
[/TD]
[TD][/TD]
[TD]5th
[/TD]
[TD]Donna&Alisha
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]&
[/TD]
[TD="align: right"]Actual
[/TD]
[TD]$15,842
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]Wyndell
[/TD]
[TD="align: right"]% Goal
[/TD]
[TD]32.33%
[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]Sam
[/TD]
[TD]Goal
[/TD]
[TD="align: right"]$69,500
[/TD]
[TD][/TD]
[TD] LAST
[/TD]
[TD]Mike&Chris
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]&
[/TD]
[TD="align: right"]Actual
[/TD]
[TD]$28,495
[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]Frank
[/TD]
[TD="align: right"]% Goal
[/TD]
[TD]41.00%
[/TD]
[/TR]
</tbody>[/TABLE]

 

[number kruncher]

Welcome to the forum.

You can use the following in F1, F4, F7, F10, F13 :

=INDEX($A$1:$A$18,MATCH(LARGE(IF($B$1:$B$18="% Goal",$C$1:$C$18),LEFT(E1,1)+0),$C$1:$C$18,0)-2)

and in F16 use :

=INDEX($A$1:$A$18,MATCH(SMALL(IF($B$1:$B$18="% Goal",$C$1:$C$18),1),$C$1:$C$18,0)-2)

Both of these need to entered with Control-Shift and Enter as these are array formulas, often refered to as CSE formula.

HTH

 

[squirl1899]

THANK YOU FOR THIS!! I initially was getting a #NUM! error but figured it out. I had "% of Goal" in my spreadsheet vs "% Goal" in my post. Once I added the "OF" in the formula it worked!!!

The only issue I now have is formatting since you can't use and array formula in a merged cell. But besides that, everything worked perfectly.

Thank you again for you help and quick response.

Welcome to the forum.

You can use the following in F1, F4, F7, F10, F13 :

=INDEX($A$1:$A$18,MATCH(LARGE(IF($B$1:$B$18="% Goal",$C$1:$C$18),LEFT(E1,1)+0),$C$1:$C$18,0)-2)

and in F16 use :

=INDEX($A$1:$A$18,MATCH(SMALL(IF($B$1:$B$18="% Goal",$C$1:$C$18),1),$C$1:$C$18,0)-2)

Both of these need to entered with Control-Shift and Enter as these are array formulas, often refered to as CSE formula.

HTH

 

[number kruncher]

Glad it worked for you.

Thanks for the feedback.