Hi,

You did not say how many column to the right that you might have, so here is
a formula (this formula is array entered - Press Shift+Ctrl+Enter to enter
it):

=INDEX(Sheet2!$A$2:$A$5,LARGE(($A1=Sheet2!$B$2:$B$ 5)*(ROW(Sheet2!$B$2:$B$5))-1,COLUMN(A1)))

Assume the data on sheet1 starts in A1 and the data on sheet2 starts on A2.
Then enter the above formula in cell B1 of sheet1 and copy it down and to the
right as far as necessary.

It will return errors for all items not found but you can either format so
that they don't display or you can delete them all with one command F5,
Special, Formulas, Errors, then Del.

Or you can use this longer version:

=IF(ISERR(INDEX(Sheet2!$A$2:$A$5,LARGE(($A1=Sheet2 !$B$2:$B$5)*(ROW(Sheet2!$B$2:$B$5))-1,COLUMN(A1)))),"",INDEX(Sheet2!$A$2:$A$5,LARGE(($ A1=Sheet2!$B$2:$B$5)*(ROW(Sheet2!$B$2:$B$5))-1,COLUMN(A1))))
--
Thanks,
Shane Devenshire


"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