Return corresponding name with ranked figures

Jasesair

Active Member
Joined
Apr 8, 2015
Messages
271
Office Version
  1. 2016
I've attempted a formula in BJ29 but have been unsuccessful. Can someone please take a quick look and let me know where I've gone wrong? The aim is to have the top 3 percentages (BI29:BI31), and then have the corresponding names from (BJ2:DI2) match with the percentage as found in (BJ23:DI23).

Book3
BHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCACBCCCDCECFCGCHCICJCKCLCMCNCOCPCQCRCSCTCUCVCWCXCYCZDADBDCDDDEDFDGDHDI
1
2MCOp1MCRe1MCSt1MCSy1MCOp2MCRe2MCSt2MCSy2MCOp3MCRe3MCSt3MCSy3MCOp4MCRe4MCSt4MCSy4CIOp1CIRe1CISt1CISy1CIOp2CIRe2CISt2CISy2CIOp3CIRe3CISt3CISy3CIOp4CIRe4CISt4CISy4CIOp5CIRe5CISt5CISy5SCOp1SCRe1SCSt1SCSy1SCOp2SCRe2SCSt2SCSy2SCOp3SCRe3SCSt3SCSy3SCOp4SCRe4SCSt4SCSy4
3
4
5
6
7
8
9
10
11
12
13Totals:
14Red102011738113331213222
15Yellow2012118141701315422351
16Green3022017155020414510443
17
18
19
20Percentages:
21Red10.00%90.91%4.55%4.55%31.82%13.64%36.36%4.55%0.00%4.55%13.64%13.64%0.00%13.64%54.55%0.00%4.55%0.00%0.00%0.00%13.64%0.00%0.00%0.00%0.00%0.00%0.00%9.09%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%9.09%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%9.09%
22Yellow20.00%4.55%9.09%4.55%81.82%63.64%77.27%0.00%0.00%4.55%13.64%68.18%0.00%18.18%9.09%0.00%9.09%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%13.64%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%22.73%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%4.55%
23Green30.00%9.09%0.00%90.91%77.27%68.18%22.73%0.00%0.00%90.91%18.18%63.64%0.00%22.73%4.55%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%18.18%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%18.18%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%13.64%
24
25
26
27Top 3, Bottom 3:
28PercentName
29Green 1st90.91%MCOp1
30Green 2nd90.91%
31Green 3rd77.27%
32Red 1st90.91%
33Red 2nd54.55%
34Red 3rd36.36%
Sheet1
Cell Formulas
RangeFormula
BJ21:DI23BJ21=IFERROR(BJ14/INDEX('[ARD Performance 210511.xlsm]Database'!$A$38:$I$38,MATCH('[ARD Performance 210511.xlsm]Results'!$BI$11,'[ARD Performance 210511.xlsm]Database'!$A$5:$I$5,0)),"")
BJ29BJ29=INDEX($BJ$2:$DI$2,AGGREGATE(15,6,(ROW($BJ$23:$DI$23)-ROW($BJ$23)+1)/($BJ$23:$DI$23=BI29),ROWS(BJ29:BJ$29)))
BI29BI29=LARGE(BJ23:DI23,1)
BI30BI30=LARGE(BJ23:DI23,2)
BI31BI31=LARGE(BJ23:DI23,3)
BI32BI32=LARGE(BJ21:DI21,1)
BI33BI33=LARGE(BJ21:DI21,2)
BI34BI34=LARGE(BJ21:DI21,3)
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,127
Messages
5,768,288
Members
425,460
Latest member
Astros1243

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top