Formula 1st 2nd 3rd

81shelly

New Member
Joined
Jul 25, 2007
Messages
24
Hey I need a formula for the 2nd highest number from a column and 3rd highest number from a column and then when i get that number that it displays the name of the person who is listed in a different column..

Thanks
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi,

=INDEX(NameRange,MATCH(LARGE(NumberRange,2),NumberRange,0))

Replace 2 with 3 to get the 3rd.

HTH
 

81shelly

New Member
Joined
Jul 25, 2007
Messages
24
Thats great thanks that formula worked for me.. just one thing if there are 2 cells with the same value is there a away around that one? For example if 2 or 3 people have come in joint 2nd is there a way to dispay all the people with the same score?

Thanks
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi,
Book1
ABCDE
1NameNumberRank2
2Name 1127Name 3
3Name 2151Name 8
4Name 3142Name 9
5Name 4109#NUM!
6Name 5118
7Name 6135
8Name 7135
9Name 8142
10Name 9142
Sheet4


Formulas

In C2 and copied down,

=RANK(B2,$B$2:$B$10)

In E2

=INDEX($A$1:$A$10,SMALL(IF($C$2:$C$10=E$1,ROW($A$2:$A$10)),ROWS($A$2:$A2)))

Confirmed with CTRL+SHIFT+ENTER

HTH
 

81shelly

New Member
Joined
Jul 25, 2007
Messages
24
Hi I have used this formula for getting 1st 2nd and 3rd..

=INDEX(NameRange,MATCH(LARGE(NumberRange,2),NumberRange,0))

But how could i put in to the formula that if the scores are 0 just to leave the result blank?

Thanks
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
Hey I need a formula for the 2nd highest number from a column and 3rd highest number from a column and then when i get that number that it displays the name of the person who is listed in a different column..

Thanks
aaTop 3 FormulaApproach 81shelly.xls
ABCDEFGHIJ
1NameScoreTopNameScoreSum of Score
2Karl2434Diana52NameTotal
3Dan45Dan45Diana52
4Diana52Cem32Dan45
5Cem32Brian32Brian32
6Brian32Cem32
7Daphne28Grand Total161
8Jan26
9Carla30
10
11
Sheet1


Formula approach...

D2 records the size of the Top N List.

E2:

=COUNTIF(B2:B9,">="&LARGE(B2:B9,D2))

F2:

Control+shift+enter...

=IF(N(G2),INDEX($A$2:$A$9,SMALL(IF($B$2:$B$9=G2,ROW($B$2:$B$9)-ROW($B$2)+1),COUNTIF($G$2:G2,G2))),"")

and copy down.

G2, copy down:

=IF(ROWS($G$2:G2)<=$E$2,LARGE($B$2:$B$9,ROWS($G$2:G2)),"")

Pivot table approach... as shown in I1:J7.
 

Forum statistics

Threads
1,181,103
Messages
5,928,077
Members
436,587
Latest member
Slicesofquince

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