Index and match stop at first answer resulting in duplicate values

[Burlgirl]

I have seen this question on the forum in the past, but every time I try to duplicate the answer - it doesn't seem to work for me...

In my first query, I am using =Small to gather the 10 lowest scores from a disc golf league. The =Small works fine. From there, I am using =index and =match to get the names of the people with the 10 lowest scores. It works, but when there are more than 1 person with the same score, the match returns only the first persons name in the list.

I've seen reference to =aggregate, but I cant seem to comprehend how that works, so every time I try to use it I have an error in my formula. I also have company controls on my computer which prevented me from installing the add-in XL2BB. So here I am...

This is the small formula which works fine: =SMALL(Scores!$D$4:$D$99,ROWS(Scores!D$4:D4))
This is the formula for first name and works fine until there is a duplicate score: =INDEX(Scores!$A$4:$A$99,MATCH(C2,Scores!$D$4:$D$99,0),0)
Last Name: =INDEX(Scores!$B$4:$B$99,MATCH(C2,Scores!$D$4:$D$99,0),0)

Copy of Summer League 23.xlsx
	A	B	C
1	PLAYER		LOW 10
2	Alexander	Pinkham	59
3	Corey	Boutin	61
4	Corey	Boutin	61
5	Miklos	Mattyasovszky	62
6	Chris	Morley	63
7	Scott	Gauvin	64
8	Scott	Gauvin	64
9	Scott	Gauvin	64
10	Scott	Gauvin	64
11	Scott	Gauvin	64
TopScores

Cell Formulas
Range	Formula
A2:A11	A2	=INDEX(Scores!$A$4:$A$99,MATCH(C2,Scores!$D$4:$D$99,0),0)
B2:B11	B2	=INDEX(Scores!$B$4:$B$99,MATCH(C2,Scores!$D$4:$D$99,0),0)
C2:C11	C2	=SMALL(Scores!$D$4:$D$99,ROWS(Scores!D$4:D4))

 

[Fluff]

Hi & welcome to MrExcel.
How about

=INDEX(Scores!$A$4:$A$99,AGGREGATE(15,6,(ROW(Scores!$D$4:$D$99)-ROW(Scores!$D$4)+1)/(Scores!$D$4:$D$99=C2),COUNTIFS(C$2:C2,C2)))

 

[Burlgirl]

Wow! That worked perfectly - but when I copy it over to column B to retrieve the last name - and change "=INDEX(Scores!$A$4:$A$99" to "Scores!$B$4:$B$99" it doesn't work. I don't understand the aggregate thing. What does the 15,6 mean? Is that where the problem is?

 

[Fluff]

Make sure the countifs is still looking at col C

 

[Burlgirl]

Nevermind - I figured it out. The problem was not with the Agregate it was at the end of the formula in the COUNTIF argument. I updated it to be cell C2 (it changed to D2 when I copied it.

THANK YOU FOR YOUR HELP!!

 

[Fluff]

You're welcome & thanks for the feedback.