Problem when using INDEX MATCH SMALL with duplicate numbers?

[Splinter1978]

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

 

[sanrv1f]

try

E2

=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

 

[Domenic]

The following will take into consideration any ties for 3rd place...

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.)

G10:

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

H10, copied down:

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

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))),"")

 

[mikerickson]

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)

 

[Splinter1978]

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