Lets say Bundle 1 has Product A, B and C
I have 3 tables:

• Bundle table: List out Bundles with whatever product and quantity under that table
• Inventory table: List out Products leftover units
• Sales Table: Record sales on products and bundles

The aim is to find the closing inventory of each Products. If it is just a normal sale, then I can just use Product Opening Quantity minus SUMIF(Product SKU Range,Product SKU,Quantity) and I will get the closing. But what if there is a Bundle that contains multiple products? How do I make excel detect Bundle 1 has Product A, B and C then deduct it in the inventory of Product A, B and C?

Hopefully it is understandable

I have tried using Vlookup but it only returns 1 value, i also tried Filter but got no idea how to make the Opening Quantity to minus it. I am at total loss