Handling duplicates with Index Match and Min

markch

New Member
Joined
Apr 27, 2017
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
Joined
Jun 12, 2014
Messages
58,246
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 27, 2017
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi Fluff,

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

Mark.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,246
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
+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.
 
Solution

markch

New Member
Joined
Apr 27, 2017
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
Joined
Jun 12, 2014
Messages
58,246
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
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?

Disable AdBlock

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
Top