Complex VLOOKUP with LEFT RIGHT function

Hi,

Herewith I attached two excel file.

01. Cost Center

Cost center file contain cost center code and respective cost center controller name.

02. Email Address
It has two taps.
one is Email list - contain all the name of cost centre controller name and their email address

Other tap is - Mapping. - contain cost center code in A column.

B column i want a function to get email address of all the cost centre controller.

Already i wrote a function it works only for one names.

Some time for one cost centre, there may be more than one cost centre controller. In this case i want email address of both in one cell with comma ",".

for example in B4 I want [email protected] , [email protected]

Please help me to sole this issue.

ThanksCost Centre.xlsxemail address.xlsx

Wow!!!. It works perfectly. Thank you very much. Is it possible to write excel formula by using worksheet functions?

Hi seventhsense,

Now I got a new idea based on your code. My concern is structure file is a big sized book contain all the details with many tabs.

I can map with external file by using the function you suggest.

for example:

=MultiClookup(VLOOKUP(A2,'C:\Users\janagan\Desktop\[Cost Centre.xlsx]Sheet1'!$A:$B,2,FALSE),'Email List'!$A$2:$A$5,'Email List'!$B$2:$B$5)

Thank you very much for your help. Thanks a lot.

Thanks you very much for your support.