Issue with index match function

[Shweta]

Hi All,

I have a table which contains Name and scores of some students.

Name	Score
Ajay Patwal	17
Ankit Dhingra	16
Beikunth	19
Garima Gupta	24
Gunwant Kaur	26
Harsha	-
Himanshu	15
Irfan	11
Kunal	-
Mukul	16
Prem	-
Priyanka Bansal	-
Rahul	17
Sachin Sharma	20
Stuti Johnson	19
Sunaina	-
Vishal Aggrawal	26

<tbody>
</tbody>

I need to find out top 3 names. I am using below function for that.

=INDEX($A$2:$A$18,MATCH(LARGE($B$2:$B$18,{1;2;3}),$B$2:$B$18,0))

with CSE.

Problem with this function is, it is giving result as Gunwant Kaur, Gunwant Kaur and Garima Gupta.
However, it should be Gurwant Kaur, Vishal Aggarwal and Garima Gupta.

I know it is happening because of the match function as it returns the first value it matches but I don't know how to deal with it. Kindly suggest.

Thanks in advance!

Regards,
Shweta Jain

 

[SyedUsman]

This will give you the top 3 in no particular order.

=INDEX($A$2:$A$18,LARGE(IF($B$2:$B$18>=LARGE($B$2:$B$18,3),ROW($A$2:$A$18)-1),ROWS($A$2:A2)))

Use with CTRL + Shift + Enter

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​
1​	Name	Score	3​
2​	Ajay Patwal	17	3​
3​	Ankit Dhingra	16	Top Performers	Top Values
4​	Beikunth	19	Gunwant Kaur	26​
5​	Garima Gupta	24	Vishal Aggrawal	26​
6​	Gunwant Kaur	26	Garima Gupta	24​
7​	Harsha	-
8​	Himanshu	15
9​	Irfan	11
10​	Kunal	-
11​	Mukul	16
12​	Prem	-
13​	Priyanka Bansal	-
14​	Rahul	17
15​	Sachin Sharma	20
16​	Stuti Johnson	19
17​	Sunaina	-
18​	Vishal Aggrawal	26

In C2 just enter:

=COUNTIFS(B2:B18,">="&LARGE(B2:B18,MIN(C1,COUNT(B2:B18))))

In C4 control+shift+enter, not just enter, and copy down:

=IF($D4="","",INDEX($A$2:$A$18,SMALL(IF($B$2:$B$18=$D4,ROW($A$2:$A$18)-ROW($A$2)+1),COUNTIFS($D$4:D4,D4))))

In D4 just enter and copy down:

=IF(ROWS($D$4:D4)>$C$2,"",LARGE($B$2:$B$18,ROWS($D$4:D4)))

 

[Shweta]

Thanks for your quick response!

Aladin Akyurek: Your formula is working fine for me however I don't want top three scores, I only want top three names. Please suggest.

Regards,
Shweta Jain

 

[Aladin Akyurek]

Thanks for your quick response!

Aladin Akyurek: Your formula is working fine for me however I don't want top three scores, I only want top three names. Please suggest.

Regards,
Shweta Jain

Row\Col	A​	B​	C​
1​	Name	Score	Top Performers
2​	Ajay Patwal	17	Gunwant Kaur
3​	Ankit Dhingra	16	Vishal Aggrawal
4​	Beikunth	19	Garima Gupta
5​	Garima Gupta	24
6​	Gunwant Kaur	26
7​	Harsha	-
8​	Himanshu	15
9​	Irfan	11
10​	Kunal	-
11​	Mukul	16
12​	Prem	-
13​	Priyanka Bansal	-
14​	Rahul	17
15​	Sachin Sharma	20
16​	Stuti Johnson	19
17​	Sunaina	-
18​	Vishal Aggrawal	26

In C2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($C$2:C2)>COUNTIFS($B$2:$B$18,">="&LARGE($B$2:$B$18,MIN(3,COUNT($B$2:$B$18)))),"",INDEX($A$2:$A$18,SMALL(IF($B$2:$B$18=LARGE($B$2:$B$18,ROWS($C$2:C2)),ROW($B$2:$B$18)-ROW($B$2)+1),SUM(IF(LARGE($B$2:$B$18,ROW($B$2:B2)-ROW($B$2)+1)=LARGE($B$2:$B$18,ROWS($C$2:C2)),1)))))

 

[NotC90]

you could also adjust your initial formula like this, to use the rownumber as an "artificial tiebreaker":

=INDEX($A$2:$A$18,MATCH(LARGE([B]$B$2:$B$18-ROW($B$2:$B$18)/999999999[/B],{1;2;3}),[B]$B$2:$B$18-ROW($B$2:$B$18)/999999999[/B],0))

Not the cleanest solution, but it shouldn't run into any problems if you are not dealing with very accurate numbers (many decimal places) and/or a huge dataset.