TO COPY MORE THAN INE RESULT FROM OTHER SHEET USING LOOKUP

[PERANISH]

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

 

[Teethless mama]

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(rngB=$A2,ROW(INDIRECT("1:"&ROWS(rngA)))),COLUMNS($B:B))))

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

 

[Max]

One play which produces the desired transformation

Illustrated in this sample:
http://freefilehosting.net/download/41mae
Extract multiple results horizontal.xls

Source data assumed in Sheet2, in A2:B2 down

In Sheet1,
List the unique Sheet2's col B source items in A11

In A2: =IF(Sheet2!$B2=A$1,ROW(),"")
Copy A2 across to D2, fill down to cover the
max expected extent of source data in Sheet1

In F2, copied down to F5: =INDEX($A$1:$D$1,ROWS($1:1))

In G2:
=IF(COLUMNS($A:A)>COUNT(OFFSET($A:$A,,ROWS($1:1)-1)),"",
INDEX(Sheet2!$A:$A,SMALL(OFFSET($A:$A,,ROWS($1:1)-1),COLUMNS($A:A))))
Copy G2 across as far as required, fill down to return the desired results.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:362 Subscribers:64
xdemechanik
---

 

[ShaneDevenshire]

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