Match multiple substrings in a cell against a lookup table and return concatenated values

Hi,

I wonder if anyone can help? I'm working with data that needs a complicated lookup and replace. I've tried various versions of vlookup formula but with no luck and was wondering if you guys could help me out?

I have a column that has comma separated values of text. I need to replace these with different text strings. I have a lookup worksheet with the replacements. If this were a simple one for one lookup it'll be simple with vlookup. But I can't find a way to get it to work with substrings.

For example, the row may be:

"^14_eng_6^,^19_ips_3^,^19_ips_7^,^32_uti_3^"

and I have a lookup worksheet like:

"^14_eng_6^" : "^C_int_8^"
"^19_ips_3^" : "^D_abs_6^"
"^19_ips_7^" : "^C_int_7^"
"^32_uti_3^" : "^C_int_2^"

I would want a function that would return a cell value like:

"^C_int_8^","^D_abs_6^","^C_int_7^","^C_int_2^"


To make matters worse, the ideal solution would remove any duplication of new values as the lookup table could allow the same result for different lookup values. Eg:

^1_eng_7^ : ^C_eng_5^
^1_eng_8^ : ^C_eng_5^



I'll attach a sample document.

I know it's a bit of a complicated one, but if anybody could point me in the right direction with functions to use, it'll be really, rellay appreciated.

sample_example.xlsx


Thanks,
Steve

UDF

Use in cell like

=VLookUps(A2,lookups!$A$2:$B$18,2)

To a standard module

Please Login or Register  to view this content.

Thank you - you are both stars!

Steve