Problem when using INDEX MATCH SMALL with duplicate numbers?

S

Splinter1978

New Member
Joined
Jun 20, 2009
Messages
5
Hi All,

I have a problem when using the following formula with duplicate numbers:

=INDEX($A10:$A19,MATCH(SMALL($D10:$D19,1),$D10:$D19,0))
=INDEX($A10:$A19,MATCH(SMALL($D10:$D19,2),$D10:$D19,0))
=INDEX($A10:$A19,MATCH(SMALL($D10:$D19,3),$D10:$D19,0))

What I am trying to do is find the three smallest numbers in a column and list the coresponding names from the example below.

A B
Ben 2
Rich 1
Brett 3
Steve 1
Russ 0

The correct result would be:

Russ
Rich
Steve

The trouble is im getting:

Russ
Rich
Rich

I am unable to put the numbers in corresponding order and there will always be duplicate numbers in the column.

Any help would be much appreciated?

Many Thanks

Ryan
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
try

E2

Code: 
=INDEX($A$10:$A$19,
     SMALL(IF($D$10:$D$19=SMALL($D$10:$D$19,ROWS(E$2:E2)),ROW($D$10:$D$19)-ROW($D$10)+1),SUM(IF($D$10:$D$19=SMALL($D$10:$D$19,
  ROWS(E$2:E2)),1,0))-SUM(IF($D$10:$D$19<=SMALL($D$10:$D$19,ROWS(E$2:E2)),1,0))+ROWS(E$2:E2)))
with Ctrl+Shift+Enter and copied down
 
Upvote 0
The following will take into consideration any ties for 3rd place...

Code: 
F10:  3

(Enter the number 3 in F10.  This indicates that you want a Bottom 3 list.
Change this as desired.  For example, for a Bottom 5 list, 
enter 5 instead.)

Code: 
G10:

=COUNTIF(D10:D14,"<="&SMALL(D10:D14,F10))

Code: 
H10, copied down:

=IF(ROWS(H$10:H10)<=$G$10,SMALL($D$10:$D$14,ROWS(H$10:H10)),"")

Code: 
I10, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(H10<>"",INDEX($A$10:$A$14,SMALL(IF($D$10:$D$14=H10,
   ROW($D$10:$D$14)-ROW($D$10)+1),COUNTIF($H$10:H10,H10))),"")
 
Upvote 0
If the entries in D are integers, these CSE formulas should do it.

=INDEX(A:A, 100*MOD(SMALL(($D$11:$D$20+ROW($D$11:$D$20)/100),1), 1), 1)
=INDEX(A:A, 100*MOD(SMALL(($D$11:$D$20+ROW($D$11:$D$20)/100),2), 1), 1)
=INDEX(A:A, 100*MOD(SMALL(($D$11:$D$20+ROW($D$11:$D$20)/100),3), 1), 1)
...

These need to be confirmed with Ctrl-Shift-Enter (Cmd+Return)
 
Last edited:
Upvote 0
Thanks very much all,

That has solved my problem and I can now continue with my project.

Your help is much appreciated.

Thanks again

Ryan
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
index match excel formula to vba code
Replies
9
Views
230
DanteAmor
DanteAmor
I
  • Solved
Index Match Help
Replies
7
Views
502
Peter_SSs
Peter_SSs
B
  • Solved
Index and match stop at first answer resulting in duplicate values
Replies
5
Views
858
Fluff
Fluff
D
  • Question
Problem with Index/Match using SMALL with duplicate values.
Replies
3
Views
617
AhoyNC
A
P
  • Question
Populating, aggregating and sorting multiple matching values in Excel
Replies
0
Views
170
Pbobas
P

Forum statistics

Threads
1,221,482
Messages
6,160,086
Members
451,616
Latest member
swgrinder

Share this page

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
Back
Top