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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Hi,

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

Replace 2 with 3 to get the 3rd.

HTH
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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
Back
Top