multiple VLOOKUP funtions

[krman]

Hello
I have been used to writing single value VLOPUP funtions but having issue when there are multiple values to the same main condition
I have 2 TABS in excel
TAB 1 have client name and some values under it.. TAB2 has the client name and multiple people covering it.. Now if I write a VLOOKUP it only catching the firts name..
This is how the TABS are set

TAB 1

Client Name Cleint ID $ Value
ssss 12536 10

TAB 2
Client Name Covering person
ssss John
ssss mike

I have 100's of clients and covering person

I have to add a VLOOPUP formaul in TAB1 which added the covering people next to the client name

Client Name Cleint ID $ Value Covering person
ssss 12536 10 John, Mike

I appreacite your help
Thanks

 

[Aladin Akyurek]

Hello
I have been used to writing single value VLOPUP funtions but having issue when there are multiple values to the same main condition
I have 2 TABS in excel
TAB 1 have client name and some values under it.. TAB2 has the client name and multiple people covering it.. Now if I write a VLOOKUP it only catching the firts name..
This is how the TABS are set

TAB 1

Client Name Cleint ID $ Value
ssss 12536 10

TAB 2
Client Name Covering person
ssss John
ssss mike

I have 100's of clients and covering person

I have to add a VLOOPUP formaul in TAB1 which added the covering people next to the client name

Client Name Cleint ID $ Value Covering person
ssss 12536 10 John, Mike

I appreacite your help
Thanks

If you download and install the free morefunc.xll add-in:

=SUBSTITUTE(MCONCAT(IF(ClientNameRange=Client,","&CoveringPersonRange,"")),",","",1)

which needs to be confirmed with control+shift+enter, not just with enter.

 

[krman]

=SUBSTITUTE(MCONCAT(IF(Sheet1!$B$1:$B$26417='Original File'!B2,","&Sheet1!D$1:D$26417,"")),",","",1)

Thanks a lot..
is there something I am missing in the fromula as it is not working
T
K

 

[Aladin Akyurek]

=SUBSTITUTE(MCONCAT(IF(Sheet1!$B$1:$B$26417='Original File'!B2,","&Sheet1!D$1:D$26417,"")),",","",1)

Thanks a lot..
is there something I am missing in the fromula as it is not working
T
K

I think that's a huge range for this formula. BTW, what happens exactly as "is not working" does not tell much?

 

[krman]

Thanks !.. I get a NAME funtion error.. is there any other way to do this
T
K

 

[Aladin Akyurek]

Thanks !.. I get a NAME funtion error.. is there any other way to do this
T
K

You get that error because you don't have the morefunc.xll add-in.

You need indeed a different way for the formula I offered won't be efficient with that big data range that you have.

 

[krman]

thanks
Any suggestions!