Find Primary Supplier for Each Product

Hello, this is a follow to another question I posted here, the link will be at the bottom. I can't seem to figure out this logic, and sanity is beginning to slip.

I'm trying to use a Store's total Inventory to Identify the Primary Supplier for each individual product. Supplier A sells Tomatoes and Green Peppers; Supplier B Tomatoes, Cheese, and Dough; and Supplier C sells Cheese and Dough.

On a scale of 1 (low stock priority) to 5 (Never stocked out), each Store and Supplier has decided the value of each product to themselves. Supplier C claims they will never run out of Cheese or Dough, while Supplier A will occasionally have Green Peppers, but will never carry Dough.

Most of Pizza Joint's inventory (48.1%) comes from Supplier B, so for every product Supplier B sells, their product value should be reflected in column C. However, since Supplier B does not sell Green Peppers, I want to look at the next highest supplier, but only if they sell the product. In this case, it is Supplier A. The product values in Column C should be Tomato:3, Cheese:1, Dough:3, Green Peppers: 2.

Here's the twist:
If Supplier A should increase their shipments to 41.40%, while Supplier B decreases to Pizza Joint to 28.00%, Supplier A will become the primary supplier for Tomatoes and Green Peppers, while Supplier C becomes the primary supplier for Cheese and Dough. The product values in Column C should be Tomato:4, Cheese:5, Dough:5, Green Peppers: 2.

What is the formula to put in Column C? I feel like I've got all the pieces, but get them together.

https://www.excelforum.com/excel-for...ml#post5628255