Convert Comma Separated Values to matching Comma Separated Strings

Thanks in advance for your review of this query!

Looking to convert a group of values in a single cell that are separated by commas into a matching list of strings separated by commas.

For instance, I'm using the following formula when only two values have been entered :

=TRIM(CONCATENATE(INDEX(M:M,MATCH(VALUE(LEFT(C4,FIND(",",C4)-1)),L:L,0))," ",INDEX(N:N,MATCH(VALUE(LEFT(C4,FIND(",",C4)-1)),L:L,0))," & ",INDEX(N:N,MATCH(VALUE(RIGHT(C4,LEN(C4)-FIND(",",C4)-1)),L:L,0))))

33205, 90149 > flavors : banana & licorice

Is there a "kinder" solution for this? What if more than two values are entered?

33205, 78596, 90149 > flavors : banana, pumpkin, licorice

See attached table for values and strings.

artikyulashun

Would M:M ever contain anything other than flavors : ? Also, if there are only 2 codes in col C, it appears you want the 2 corresponding values from N:N separated by &, but it there are 3 or more you want a comma-separated list.

This is an example of something Excel does very poorly, expecially in a version as old as Excel 2007. From my perspective, you'd be better off using VBA.

Fair observation on the "&". I think a comma for each flavor would suffice.

No. M:M would contain other naming conventions.

Honestly, I'm not versed in VBA. Do you have something in mind?

Appreciate the coding that went into this hrlngrv. I'll roll up my sleeves and see if I can follow along ...