Find and return multiple values

B

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
 
J

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top