# Handling duplicates with Index Match and Min

#### markch

##### New Member
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:

Excel Formula:
``=INDEX(\$A\$2:\$A\$11,MATCH(SMALL(\$B\$2:\$B\$11,1),\$B\$2:\$B\$11,0))``
then
Excel Formula:
``=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
ABCDEFG
1NameCountPerson 1Person 2Person 3Person 4
2Frank205CarlFrankFrankMickey
3Karen258
4Jamie256
5Lillian308
6Ian279
7Kelly205
8Carl203
9Clem297
10Mickey235
11Ruby246
Sheet3
Cell Formulas
RangeFormula
D2D2=INDEX(\$A\$2:\$A\$11,MATCH(SMALL(\$B\$2:\$B\$11,1),\$B\$2:\$B\$11,0))
E2E2=INDEX(\$A\$2:\$A\$11,MATCH(SMALL(\$B\$2:\$B\$11,2),\$B\$2:\$B\$11,0))
F2F2=INDEX(\$A\$2:\$A\$11,MATCH(SMALL(\$B\$2:\$B\$11,3),\$B\$2:\$B\$11,0))
G2G2=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,

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### Fluff

##### MrExcel MVP, Moderator
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

##### New Member
Hi Fluff,

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

Mark.

#### Fluff

##### MrExcel MVP, Moderator
+Fluff 1.xlsm
ABCDEFG
1NameCountPerson 1Person 2Person 3Person 4
2Frank205CarlFrankKellyMickey
3Karen258
4Jamie256
5Lillian308
6Ian279
7Kelly205
8Carl203
9Clem297
10Mickey235
11Ruby246
12
Lists
Cell Formulas
RangeFormula
D2:G2D2=INDEX(SORTBY(A2:A11,B2:B11,1),SEQUENCE(,4))
Dynamic array formulas.

#### markch

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

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
2
Views
101
Replies
0
Views
252
Replies
6
Views
149
Replies
3
Views
287
Replies
7
Views
102

1,132,913
Messages
5,655,919
Members
418,253
Latest member
TheJackal26

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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