Issue with index match function

[Shweta]

Hi All,

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


[TABLE="width: 205"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]Ajay Patwal[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]Ankit Dhingra[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]Beikunth[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]Garima Gupta[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]Gunwant Kaur[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]Harsha[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Himanshu[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Irfan[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]Kunal[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Mukul[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]Prem[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Priyanka Bansal[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Rahul[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]Sachin Sharma[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Stuti Johnson[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]Sunaina[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Vishal Aggrawal[/TD]
[TD]26[/TD]
[/TR]
</tbody>[/TABLE]

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]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr][tr][td]

1​

[/td][td] Name[/td][td] Score[/td][td]

3​

[/td][td][/td][/tr]
[tr][td]

2​

[/td][td] Ajay Patwal[/td][td] 17[/td][td]

3​

[/td][td][/td][/tr]
[tr][td]

3​

[/td][td] Ankit Dhingra[/td][td] 16[/td][td]Top Performers[/td][td]Top Values[/td][/tr]
[tr][td]

4​

[/td][td] Beikunth[/td][td] 19[/td][td]Gunwant Kaur[/td][td]

26​

[/td][/tr]
[tr][td]

5​

[/td][td] Garima Gupta[/td][td] 24[/td][td]Vishal Aggrawal[/td][td]

26​

[/td][/tr]
[tr][td]

6​

[/td][td] Gunwant Kaur[/td][td] 26[/td][td]Garima Gupta[/td][td]

24​

[/td][/tr]
[tr][td]

7​

[/td][td] Harsha[/td][td] -[/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td] Himanshu[/td][td] 15[/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td] Irfan[/td][td] 11[/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td] Kunal[/td][td] -[/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td] Mukul[/td][td] 16[/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td] Prem[/td][td] -[/td][td][/td][td][/td][/tr]
[tr][td]

13​

[/td][td] Priyanka Bansal[/td][td] -[/td][td][/td][td][/td][/tr]
[tr][td]

14​

[/td][td] Rahul[/td][td] 17[/td][td][/td][td][/td][/tr]
[tr][td]

15​

[/td][td] Sachin Sharma[/td][td] 20[/td][td][/td][td][/td][/tr]
[tr][td]

16​

[/td][td] Stuti Johnson[/td][td] 19[/td][td][/td][td][/td][/tr]
[tr][td]

17​

[/td][td] Sunaina[/td][td] -[/td][td][/td][td][/td][/tr]
[tr][td]

18​

[/td][td] Vishal Aggrawal[/td][td] 26[/td][td][/td][td][/td][/tr]
[/table]

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

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr][tr][td]

1​

[/td][td] Name[/td][td] Score[/td][td]Top Performers[/td][/tr]
[tr][td]

2​

[/td][td] Ajay Patwal[/td][td] 17[/td][td]Gunwant Kaur[/td][/tr]
[tr][td]

3​

[/td][td] Ankit Dhingra[/td][td] 16[/td][td]Vishal Aggrawal[/td][/tr]
[tr][td]

4​

[/td][td] Beikunth[/td][td] 19[/td][td]Garima Gupta[/td][/tr]
[tr][td]

5​

[/td][td] Garima Gupta[/td][td] 24[/td][td][/td][/tr]
[tr][td]

6​

[/td][td] Gunwant Kaur[/td][td] 26[/td][td][/td][/tr]
[tr][td]

7​

[/td][td] Harsha[/td][td] -[/td][td][/td][/tr]
[tr][td]

8​

[/td][td] Himanshu[/td][td] 15[/td][td][/td][/tr]
[tr][td]

9​

[/td][td] Irfan[/td][td] 11[/td][td][/td][/tr]
[tr][td]

10​

[/td][td] Kunal[/td][td] -[/td][td][/td][/tr]
[tr][td]

11​

[/td][td] Mukul[/td][td] 16[/td][td][/td][/tr]
[tr][td]

12​

[/td][td] Prem[/td][td] -[/td][td][/td][/tr]
[tr][td]

13​

[/td][td] Priyanka Bansal[/td][td] -[/td][td][/td][/tr]
[tr][td]

14​

[/td][td] Rahul[/td][td] 17[/td][td][/td][/tr]
[tr][td]

15​

[/td][td] Sachin Sharma[/td][td] 20[/td][td][/td][/tr]
[tr][td]

16​

[/td][td] Stuti Johnson[/td][td] 19[/td][td][/td][/tr]
[tr][td]

17​

[/td][td] Sunaina[/td][td] -[/td][td][/td][/tr]
[tr][td]

18​

[/td][td] Vishal Aggrawal[/td][td] 26[/td][td][/td][/tr]
[/table]

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.