Find a Match in Multiple Places & Return Multiple Values

[Toria]

Hello,

I've spent hours on this and still can't get the formula right. I have a
list of phone numbers in column A on worksheet 1 and another list phone
numbers in column A on worksheet 2. I need a formula on worksheet 1 that
says if the phone number matches on worksheet 2, then place the contents from
column D on worksheet 2 into worksheet 1. The phone number will match more
than once on worksheet 2, however, so I need will need Excel to be able to
return multiple values (which is all text). Thank you in advance for any
help you can provide!

 

[Max]

Here's one way to deliver it dynamically w/o too much fuss ..

Illustrated in this sample:
http://www.freefilehosting.net/download/3igc5
Extracting dups from sht2 horizontally for uniques in sht1.xls

Data assumed in row 2 down in both Sheet1/2
where Sheet1, col A contains the unique phone numbers &
Sheet2, col A contains the duplicate phone numbers (in any order)

In Sheet2,
In F2:
=IF($A2="","",IF($A2=OFFSET(Sheet1!$A$1,COLUMNS($A:A),),ROW(),""))
Copy F2 across by as many cols as there are unique phone numbers
listed/expected in Sheet1, then fill down to cover the max expected extent of
data in col A. Leave the top row (ie F1 across blank)

Then in Sheet1,
In B2:
=IF(COLUMNS($A:A)>COUNT(OFFSET(Sheet2!$E:$E,,ROWS($1:1))),"",INDEX(Sheet2!$D:$D,SMALL(OFFSET(Sheet2!$E:$E,,ROWS($1:1)),COLUMNS($A:A))))
Copy B2 across by the maximum expected number of duplicates per any unique
phone number (in Sheet2), say across by 6 cols? to G2, fill down. This will
return the required results, ie the duplicate text from Sheet2's col D into
cols B to G for each of the unique phone numbers listed in A2 down, with all
results neatly bunched to the left.

 

[Toria]

Thanks, Max! I really appreciate it!!

Here's one way to deliver it dynamically w/o too much fuss ..

Illustrated in this sample:
http://www.freefilehosting.net/download/3igc5
Extracting dups from sht2 horizontally for uniques in sht1.xls

Data assumed in row 2 down in both Sheet1/2
where Sheet1, col A contains the unique phone numbers &
Sheet2, col A contains the duplicate phone numbers (in any order)

In Sheet2,
In F2:
=IF($A2="","",IF($A2=OFFSET(Sheet1!$A$1,COLUMNS($A:A),),ROW(),""))
Copy F2 across by as many cols as there are unique phone numbers
listed/expected in Sheet1, then fill down to cover the max expected extent of
data in col A. Leave the top row (ie F1 across blank)

Then in Sheet1,
In B2:
=IF(COLUMNS($A:A)>COUNT(OFFSET(Sheet2!$E:$E,,ROWS($1:1))),"",INDEX(Sheet2!$D:$D,SMALL(OFFSET(Sheet2!$E:$E,,ROWS($1:1)),COLUMNS($A:A))))
Copy B2 across by the maximum expected number of duplicates per any unique
phone number (in Sheet2), say across by 6 cols? to G2, fill down. This will
return the required results, ie the duplicate text from Sheet2's col D into
cols B to G for each of the unique phone numbers listed in A2 down, with all
results neatly bunched to the left.

 

[Max]

Delighted to hear that, Toria.
Thanks for posting back.