So, I have a Product that is made of multiple materials, labeled with material IDs. I need to pull the multiple material IDs from the cell and find their associated costs from another table, and then sum them all up to find the total production cost. This is easy to do when there is only one material needed for the product with a simple xlookup, however when there are multiple material IDs it doesn’t work. This is for a little made up project where I’m starting a business selling jewelry. I apologize if the answer is very simple, I am not an Excel expert. Anyhow the help is appreciated, thank you!

I know I could work around this by first making a third table only for finding the production cost and having each material ID in its own cell, but I was really hoping I could find a way to avoid this and figure out how to xlookup and sum multiple materials based on their ID from one cell.

As you can see in cell K12 the necklace is made up of 3 different materials (three material IDs). How do I xlookup the three and sum them for the total production cost?

Here is a photo for reference:

And here is my code for the “Production Cost” column:

=SUM(XLOOKUP([@[Material IDs Needed]],Table1[Material ID],Table1[Material Cost]))