# Formula 1st 2nd 3rd

#### 81shelly

##### New Member
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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Hi,

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

Replace 2 with 3 to get the 3rd.

HTH

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

Hi,
Book1
ABCDE
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

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

Hi,

Go to tools > option > view > uncheck Zero values

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.

Replies
2
Views
285
Replies
3
Views
865
Replies
1
Views
116
Replies
10
Views
217
Replies
12
Views
230

1,217,764
Messages
6,138,474
Members
450,141
Latest member
Hal5000

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

### Which adblocker are you using?

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

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