Find and return multiple values

[BubbleGum]

Hi,
I've an urgent question here in regards to return multiple values and hope
you can help.

Sheet1
A B C D E ...
1 1001
2 1002
3 1003
4 1004

Sheet2
A B
1 1002 Y01
2 1001 B01
3 1004 Z08
4 1001 D04
5 1001 J05
6 1004 H22

Let's say in Sheet1 cell B1, I type in =vlookup(A1, Sheet2!A:B,2,false), it
returns the value "B01". Is it possible to type in formula in cell C1, D1 to
return the remaining match, which are "D04" and "J05"?

Your reply is much appreciated,
BubbleGum

 

[JMB]

Try in B1
=INDEX(Sheet2!$B$1:$B$6,SMALL(IF(Sheet2!$A$1:$A$6=$A1,ROW(Sheet2!$B$1:$B$6)-ROW(Sheet2!$B$1)+1),COLUMNS($B1:B1)))

array entered w/ Cntrl+Shift+Enter. Copy across until you get #NUM

If you prefer blank instead of #NUM then try

=IF(SUM(--(Sheet2!$A$1:$A$6=$A1))<COLUMNS($B1:B1),"",INDEX(Sheet2!$B$1:$B$6,SMALL(IF(Sheet2!$A$1:$A$6=$A1,ROW(Sheet2!$B$1:$B$6)-ROW(Sheet2!$B$1)+1),COLUMNS($B1:B1))))

also array entered.

 

[PapaDos]

Enter this array formula in B1
=IF( COLUMNS( $B1:B1 ) > COUNTIF( Sheet2!$A$1:$A$6, $A1 ), "-", INDEX(
Sheet2!$B:$B, SMALL( IF( $A1 = Sheet2!$A$1:$A$6, ROW( Sheet2!$A$1:$A$6 ) ),
COLUMNS( $B1:B1 ) ) ) )

Drag/Fill as needed...