Handling duplicates with Index Match and Min

[markch]

Hi Everybody,

I'm trying to return values next to the smallest value in a list. I've tried searching but it's very difficult to find the right description to match what I'm doing.

In the below example I have a list of names with a number next to them. I want to return the names of people with the four lowest numbers which I have done using:

=INDEX($A$2:$A$11,MATCH(SMALL($B$2:$B$11,1),$B$2:$B$11,0))

then

=INDEX($A$2:$A$11,MATCH(SMALL($B$2:$B$11,2),$B$2:$B$11,0))

and so on.

However there are some names that have duplicate numbers which causes the formula to return the same name twice, rather than the duplicates in turn.

Book1
	A	B	C	D	E	F	G
1	Name	Count		Person 1	Person 2	Person 3	Person 4
2	Frank	205		Carl	Frank	Frank	Mickey
3	Karen	258
4	Jamie	256
5	Lillian	308
6	Ian	279
7	Kelly	205
8	Carl	203
9	Clem	297
10	Mickey	235
11	Ruby	246
Sheet3

Cell Formulas
Range	Formula
D2	D2	=INDEX($A$2:$A$11,MATCH(SMALL($B$2:$B$11,1),$B$2:$B$11,0))
E2	E2	=INDEX($A$2:$A$11,MATCH(SMALL($B$2:$B$11,2),$B$2:$B$11,0))
F2	F2	=INDEX($A$2:$A$11,MATCH(SMALL($B$2:$B$11,3),$B$2:$B$11,0))
G2	G2	=INDEX($A$2:$A$11,MATCH(SMALL($B$2:$B$11,4),$B$2:$B$11,0))

Can anybody help with how to construct a formula that would show the duplicates in order rather than miss one?

Many thanks,

Mark,

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[markch]

Hi Fluff,

Thanks, I've updated that now. This is Excel 365 for Mac.

Mark.

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1	Name	Count		Person 1	Person 2	Person 3	Person 4
2	Frank	205		Carl	Frank	Kelly	Mickey
3	Karen	258
4	Jamie	256
5	Lillian	308
6	Ian	279
7	Kelly	205
8	Carl	203
9	Clem	297
10	Mickey	235
11	Ruby	246
12
Lists

Cell Formulas
Range	Formula
D2:G2	D2	=INDEX(SORTBY(A2:A11,B2:B11,1),SEQUENCE(,4))
Dynamic array formulas.

 

[markch]

That's perfect, thank you Fluff! I had wondered about getting Excel to sort and pick out the results I wanted, but wasn't aware of the Sequence function.

Thanks again,

Mark.

 

[Fluff]

You're welcome & thanks for the feedback.