VLOOKUP and return multiple values conatenate

[sk2018]

I am using Microsoft Excel 2016.

I need to use vlookup and return the multiple values with concatenation.
Based on Value in Column A, I need to return the reference value in Access worksheet.
I need the B2 to show Project Manager, Sponsor
B3 to show Business Analyst, IT Lead, Functional Lead
B4 to show Project Manager

How can we do that in Excel? I have tried googling around.. but it is to use TEXTJOIN which is not available in excel 2016.
Appreciate your help in this.

 

[Peter_SSs]

I am using Microsoft Excel 2016.

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using. (Don’t forget to scroll down & ‘Save’)

I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Worksheet functions in Excel 2016 (& earlier) do not lend themselves well to this sort of text joining. I would employ a user-defined function in vba. To implement ..
1. Right click any sheet name tab in your workbook and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function ListAccess(rData As Range, sID As String) As String
  sID = "|" & sID & "|"
  ListAccess = Replace(Join(Filter(Application.Transpose(Evaluate("""|""&" & rData.Columns(1).Address(External:=True) & "&""|""&" & rData.Columns(2).Address(External:=True))), sID), ", "), sID, "")
End Function

sk2018.xlsm
	A	B
1	ID	Access
2	1	PM
3	2	BA
4	1	S
5	3	PM
6	2	ITL
7	2	FL
Access

sk2018.xlsm
	A	B
1	ID	Access
2	1	PM, S
3	2	BA, ITL, FL
4	3	PM
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=ListAccess(Access!A$2:B$7,A2)

 

[sk2018]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using. (Don’t forget to scroll down & ‘Save’)

I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Worksheet functions in Excel 2016 (& earlier) do not lend themselves well to this sort of text joining. I would employ a user-defined function in vba. To implement ..
1. Right click any sheet name tab in your workbook and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function ListAccess(rData As Range, sID As String) As String
  sID = "|" & sID & "|"
  ListAccess = Replace(Join(Filter(Application.Transpose(Evaluate("""|""&" & rData.Columns(1).Address(External:=True) & "&""|""&" & rData.Columns(2).Address(External:=True))), sID), ", "), sID, "")
End Function

sk2018.xlsm
	A	B
1	ID	Access
2	1	PM
3	2	BA
4	1	S
5	3	PM
6	2	ITL
7	2	FL
Access

sk2018.xlsm
	A	B
1	ID	Access
2	1	PM, S
3	2	BA, ITL, FL
4	3	PM
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=ListAccess(Access!A$2:B$7,A2)

@Peter_SSs,

Thanks a lot.
I have updated my profile and noted on XL2BB.

The vb code is working perfectly great.


Thanks =)

 

[Peter_SSs]

The vb code is working perfectly great.


Thanks =)

Good news! .. and you are welcome.

Thanks for updating your profile.