Match between multiple values in single cells and lookup-list

Hi,

As you can see in the attached Excel-file that I have two sheets:
- "Segment-scoring": I have multiple customers and products for each segment. Note: I can't change this format. 2 separators are used in the cells: ";#", semicolon and hashtag.
- "Lookup-scores": It gives the scores for each customer and product type.

Question: I need to calculate the aggregated customer score (Column-C) and aggregated product score (Column E) for each segment by using the scores in the "Lookup-scores", maybe by using the Vlookup-function?
Maybe retaining multiple values as arrays and matching them with the lookup-table. Or is there any simpler solution without array?

Limitation: It is not efficient to split up the cells into new columns. In my real data, I have many values in each cell, and I got over 300 columns when I splitted up the cells with multiple values.

Thanks in advance.

C2

Formula:

Please Login or Register  to view this content.

E2

Please Login or Register  to view this content.