# Help for formula to find Top 3 value in excel

#### emran06

##### New Member
Dear Experts,
I am facing problem to make summary sheet of top 3 value from large number of value of my excel sheet like-
Sheet1

 Group Name Value 1 Nashid 200145 2 Kashem 188516 1 Jabed 184198 2 Rubel 172569 3 Romel 168251 1 Eman 156622 2 Hasin 152304 3 Nasim 136357 3 Hasan 120410 2 Monem 104463 1 Rashed 88516 3 Nizam 72569

<tbody>
</tbody>

Now I want to make summary in sheet2 top 5 value among the list of sheet1 separately from group 1, 2 & 3.
If group number is 1 in sheet1 then top 3 value will be like this-
Group1
 Name Value Nashid 200145 Jabed 184198 Eman 156622

<tbody>
</tbody>

If group number is 2 in sheet1 then top 3 value will be like this-
Group2
 Name Value Rubel 172569 Hasin 152304 Monem 104463

<tbody>
</tbody>

If group number is 3 in sheet1 then top 3 value will be like this-
 Name Value Romel 168251 Nasim 136357 Hasan 120410

<tbody>
</tbody>

Best Regards

Emran

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### DILIPandey

##### Well-known Member
HI Emran,

Try looking at LARGE function where you can get top 1 /2 /3 values easily.

Regards,
DILIPandey

#### Peter_SSs

##### MrExcel MVP, Moderator
If your Values in sheet 1 are always in descending order like your sample, the problem is a bit easier, but I've assumed they may not be in order so I've changed the sample data a bit.
Notice that I have also allowed for ties within groups - see green cells.

Formula in E2 copied down as far as you might ever need. (Column E could then be hidden if you want)

Excel Workbook
ABCDE
1GroupNameValue
21Nashid200,1451|1
32Kashem188,5162|2
41Jabed184,1981|2
52Rubel172,5692|4
63Romel168,2513|1
71Eman156,6221|3
82Hasin188,5162|3
93Nasim136,3573|2
103Hasan120,4103|3
112Monem504,4632|1
121Rashed88,5161|4
133Nizam72,5693|4
14
Sheet1

Formula in A4 copied across and down.

Excel Workbook
AB
1Group2
2
3NameValue
4Monem504,463
5Kashem188,516
6Hasin188,516
7
Sheet2

#### emran06

##### New Member
If your Values in sheet 1 are always in descending order like your sample, the problem is a bit easier, but I've assumed they may not be in order so I've changed the sample data a bit.
Notice that I have also allowed for ties within groups - see green cells.

Formula in E2 copied down as far as you might ever need. (Column E could then be hidden if you want)

Sheet1

 * A B C D E 1 Group Name Value * * 2 1 Nashid 200,145 * 1|1 3 2 Kashem 188,516 * 2|2 4 1 Jabed 184,198 * 1|2 5 2 Rubel 172,569 * 2|4 6 3 Romel 168,251 * 3|1 7 1 Eman 156,622 * 1|3 8 2 Hasin 188,516 * 2|3 9 3 Nasim 136,357 * 3|2 10 3 Hasan 120,410 * 3|3 11 2 Monem 504,463 * 2|1 12 1 Rashed 88,516 * 1|4 13 3 Nizam 72,569 * 3|4 14 * * * * *

<tbody>
</tbody>

 Cell Formula E2 =A2&"|"&COUNTIFS(A\$2:A\$13,A2,C\$2:C\$13,">"&C2)+COUNTIFS(A\$2:A2,A2,C\$2:C2,C2)

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Formula in A4 copied across and down.

Sheet2

 * A B 1 Group 2 2 * * 3 Name Value 4 Monem 504,463 5 Kashem 188,516 6 Hasin 188,516 7 * *

<tbody>
</tbody>

 Cell Formula A4 =INDEX(Sheet1!B\$2:B\$13,MATCH(\$B\$1&"|"&ROWS(A\$4:A4),Sheet1!\$E\$2:\$E\$13,0))

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Dear Sir,
Thank you very much for your help. Actually I wanted formula for top 3 value (it may be top 10 or more) from each group. I would find name with vlookup formula. But I need top 3 value.
If group number is 1 then top 3 large value will be as follows-

 Name Value Nashid 200145 Jabed 184198 Eman 156622

<tbody>
</tbody>

I have used large formula and it working great but problem is, it find top value among the all data but my requirement is top 3 within the group.

Regards

Emran

#### ravi 1986

##### New Member

in sheet2 put in Cell A1 Group and in Cell B1 put data validation of 1,2 and 3
then in A2 type "Name", in B2 type "Value"
and in cell A3 put the formula : =INDEX(Sheet1!\$B\$2:\$C\$13,MATCH(LARGE((\$B\$1=Sheet1!\$A\$2:\$A\$13)*(Sheet1!\$C\$2:\$C\$13),ROW(A1)),Sheet1!\$C\$2:\$C\$13,0),MATCH(A\$2,Sheet1!\$B\$1:\$C\$1,0)) with CSE
 Group 2 Name Value Kashem 188516 Rubel 172569 Hasin 152304

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

#### emran06

##### New Member
HI Emran,

Try looking at LARGE function where you can get top 1 /2 /3 values easily.

Regards,
DILIPandey

Thanks for your comments, Actually I used large function and it working great but I need large to small from each group.

Hi ravi 1986,

Thanks again

Emran

Last edited:

#### Peter_SSs

##### MrExcel MVP, Moderator
Dear Sir,
Thank you very much for your help. Actually I wanted formula for top 3 value (it may be top 10 or more) from each group. I would find name with vlookup formula. But I need top 3 value.
(remember column E can be hidden)

Excel Workbook
ABCDE
1GroupNameValue
21Nashid2001451|1
32Kashem1885162|1
41Jabed1841981|2
52Rubel1725692|2
63Romel1682513|1
71Eman1566221|3
82Hasin1523042|3
93Nasim1363573|2
103Hasan1204103|3
112Monem1044632|4
121Rashed885161|4
133Nizam725693|4
Sheet1

.. and searching for Group 1 my results are ...

Excel Workbook
AB
1Group1
2
3NameValue
4Nashid200,145
5Jabed184,198
6Eman156,622
7
Sheet2

Could you explain in what way this is not what you asked for so that I can modify where necessary?

If you want more than 3, copy the formulas down further.

If you want all the results from the given group say so (but it isn't what you asked for).

Last edited:

#### Peter_SSs

##### MrExcel MVP, Moderator
If your Values in sheet 1 are always in descending order like your sample, the problem is a bit easier ...
You also didn't comment on this part of my earlier post, which could affect the simplest way to get the required results.

#### Peter_SSs

##### MrExcel MVP, Moderator
Hi ravi 1986,
Your call, but I'd be careful using those formulas.

a) Is it ever possible that two people from the same group could have the same value? Check the results for top 3 for Group 1 with this data in Sheet1.

Excel Workbook
ABC
1GroupNameValue
21Nashid200,145
31Kashem188,516
41Jabed188,516
Sheet1

b) It may not happen, but what if somebody later decides to insert a new row 1 into Sheet2, say for some extra headings, when using Ravi's formula. Try it and look at the results.

Last edited:

Replies
1
Views
233
Replies
5
Views
7K

1,129,579
Messages
5,637,209
Members
416,961
Latest member
sigrid6940

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