XL-2007:
=IFERROR(INDEX(rngA,SMALL(IF(rngB=$A2,ROW(INDIRECT ("1:"&ROWS(rngA)))),COLUMNS($B:B))),"")

ctrl+shift+enter, not just enter
copy across and down as far as needed


Prior to XL-2007:
=IF(ISERR(SMALL(IF(rngB=$A2,ROW(INDIRECT("1:"&ROWS (rngA)))),COLUMNS($B:B))),"",INDEX(rngA,SMALL(IF(r ngB=$A2,ROW(INDIRECT("1:"&ROWS(rngA)))),COLUMNS($B :B))))

ctrl+shift+enter, not just enter
copy across and down as far as needed



"PERANISH" wrote:

Sirs

I want formula for one criteria & with more than one result. Please refer
example

Sheet-1 Sheet-2
Col-a Col-a col-b
aa-01 xy-001 aa-02
aa-02 xs-001 cc-01
cc-01 xx-002 aa-02
cc-02 yz-001 cc-02

I want result like in Sheet-1 at Col-b & C
Sheet-1
Col-a col-b col-c
aa-01
aa-02 xy-001 xx-002
cc-01 xs-001
cc-02 yz-001

Datas will be more than 10000 entries.

Please help on this

Regards
Peranish