Vlookup return multiple values from another spreadsheet...Help!

[nicole327]

I have been trying to figure this one out on my own and i can't seem to get it. I have a spreadsheet with names and i need to lookup email address for them and some have more than 1. I know there is a way but i just can't figure it out. Below is an example of my 2 worksheets and the end result i am looking for.

WORKSHEET A

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]S123456[/TD]
[TD]Smith, John[/TD]
[/TR]
[TR]
[TD]D654321[/TD]
[TD]Doe, Jane[/TD]
[/TR]
[TR]
[TD]H234567[/TD]
[TD]Hall, Sam[/TD]
[/TR]
[TR]
[TD]G345678[/TD]
[TD]Good, Bill[/TD]
[/TR]
[TR]
[TD]A456789[/TD]
[TD]Apple, Jill[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

WORKSHEET B
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]S123456[/TD]
[TD]TEST1@GMAIL.COM[/TD]
[/TR]
[TR]
[TD]S123456[/TD]
[TD]TEST2@GMAIL.COM[/TD]
[/TR]
[TR]
[TD]D654321[/TD]
[TD]TEST3@GMAIL.COM[/TD]
[/TR]
[TR]
[TD]D654321[/TD]
[TD]TEST4@GMAIL.COM[/TD]
[/TR]
[TR]
[TD]H234567[/TD]
[TD]TEST5@GMAIL.COM[/TD]
[/TR]
[TR]
[TD]G345678[/TD]
[TD]TEST6@GMAIL.COM[/TD]
[/TR]
[TR]
[TD]G345678[/TD]
[TD]TEST7@GMAIL.COM[/TD]
[/TR]
[TR]
[TD]A456789[/TD]
[TD]TEST8@GMAIL.COM[/TD]
[/TR]
</tbody>[/TABLE]


END RESULT I WOULD LIKE

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]S123456[/TD]
[TD]TEST1@GMAIL.COM; TEST2@GMAIL.COM[/TD]
[/TR]
[TR]
[TD]D654321[/TD]
[TD]TEST3@GMAIL.COM; TEST4@GMAIL.COM[/TD]
[/TR]
[TR]
[TD]H234567[/TD]
[TD]TEST5@GMAIL.COM[/TD]
[/TR]
[TR]
[TD]G345678[/TD]
[TD]TEST6@GMAIL.COM; TEST7@GMAIL.COM[/TD]
[/TR]
[TR]
[TD]A456789[/TD]
[TD]TEST8@GMAIL.COM[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

In C2 of Sheet1 (worksheet a) control+shift+enter, not just enter, and copy down:

=TEXTJOIN("; ",TRUE,IF(Sheet2!$A$2:$A$9=$A2,Sheet2!$B$2:$B$9,""))


where Sheet2 = worksheet b.

 

[nicole327]

This is what i get back #NAME ?
i must be doing something wrong. In C2 i hit control+shift+enter and nothing happened but i pasted anyway and got that result [TABLE="width: 173"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

This is what i get back #NAME ?
i must be doing something wrong. In C2 i hit control+shift+enter and nothing happened but i pasted anyway and got that result [TABLE="width: 173"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

Is the TEXTJOIN function available on your Excel version?

 

[nicole327]

No, i have 2016 version.

 

[Aladin Akyurek]

No, i have 2016 version.

If the native function TEXTJOIN is not available on your system...

In C2 of Sheet1 control+shift+enter, not just enter, and copy down:

=REPLACE(aconcat(IF(Sheet2!$A$2:$A$9=$A2,Sheet2!$B$2:$B$9,"")),1,2,"")

For this formula to work, add the following code for ACONCAT as a Module to your workbook using Alt+F11...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function