Search cell for four letter code/s and return matching email/s.

I'm wanting to use a formula or function that searches cells for four letter codes and returns any corresponding email assigned to that code in the table. I have a table with the codes and emails in them eg A1:B8 where column A=codes and B=emails.
In the searched cells there can be:
a) no codes
b) one code or
c) more than one code.
In each instance I would want
a) no email returned
b) the one email for that code returned
c) each email for each code returned.
A VLOOKUP works well when there is no code or only one code in the cell but when there is more than one code only the first code/email is returned.
I've attached an example document that shows an example of how the search works and how only the 1st code/email is returned.

EXAMPLE EXCEL DOC.xlsx

If anyone can help that'd be greatly appreciated.

Hi FDibbins,

Thanks for the welcome and quick response.
Cell D7 in the example should display two emails for the two codes (EFGH & QRST) that are in C7
Thanks,
Tim

Originally Posted by TimHerbert

Hi FDibbins,

Thanks for the welcome and quick response.
Cell D7 in the example should display two emails for the two codes (EFGH & QRST) that are in C7
Thanks,
Tim

Hi TemHerber,
You can try this UDF

Please Login or Register  to view this content.

with formula at E2:

Please Login or Register  to view this content.

Originally Posted by FDibbins

If you will only have 2 combinations, then you can use this, copied down...

=VLOOKUP(C7,$A$2:$B$8,2)&IF(ISNUMBER(SEARCH(" ",C7,1))," "&VLOOKUP(MID(C7,SEARCH(" ",C7,1)+1,99),$A$2:$B$8,2),"")

If you will have more han 2, it gets messy, but you need to add...
" "&VLOOKUP(MID(C7,SEARCH(" ",C7,1)+1,99),$A$2:$B$8,2)
but modify the search() function in each case (bolded) to search for the extra spaces

This works perfectly for two codes. Thanks a lot.
I'm a little unclear as to what exactly it is I'm adding where if there are more than 2 codes to be found.
If the maximum number of codes to find was 13 what would the final formula look like?

Thanks so much for your help.

Originally Posted by huuthang_bd

Hi TemHerber,
You can try this UDF

Please Login or Register  to view this content.

with formula at E2:

Please Login or Register  to view this content.

Hi huuthang,
I tried this and it worked really well for the example sheet but when I tried it in my actual data it wasn't successful as I had to adjust the E2 formula to provide information from another sheet.

Thanks,
Tim

Originally Posted by TimHerbert

Hi huuthang,
I tried this and it worked really well for the example sheet but when I tried it in my actual data it wasn't successful as I had to adjust the E2 formula to provide information from another sheet.

Thanks,
Tim

Hi Tim,
Can you attach the file which the UDF not working?

huuthang

Originally Posted by huuthang_bd

Hi Tim,
Can you attach the file which the UDF not working?

huuthang

Hi huuthang,
After having a play I've managed to get it working.
Thank you very much for your help!

Tim