I have a table (Table a) with a list of materials and material pricing. the columns are material_name and material_price.

I have a separate table (Table B) that has the assembly name, assembly materials and assembly price. The assembly materials is a list contained in a single cell. Each material is on its own row

I am trying to create a sum of material prices for each assembly. In Table B Cell F3 contains 4 materials on their own line. In cell G3 I want to look at each material in cell F3 reference against table A to find the correct price for each material and sum all of the materials prices to create a total assembly price that includes the price of all materials listed in cell F3

Assemblies are of varying size. some are a single material some are up to 10 materials.

I'd really like to be able to maintain the formatting for the assemblies as it's listed above for easier readability. Any help would be appreciated.

Expected Result:

Sum of all the items contained in the assembly. Sum will be derived from material price in table A

Example Table

Using the example above: In cell F3 there is a list of assembly materials. For each of the materials in cell F3 I would like to grab the price of that material from Table A and sum it against the rest of the materials in cell F3. I would like the sum of those materials to be presented in cell G3 as the Assembly Price.

Here's a link for the Sheet:

https://docs.google.com/spreadsheets/d/1anebCM6jDAYlksZm7ASdrj9mnwhJozKBQKDv6PpxDoA/edit?usp=sharing

CodePudding user response：

Try, in G5

=sum(arrayformula(iferror(VLOOKUP(split(F5,char(10)),B:C,2,0),0)))