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

Solution Verified

I had to modify your solution to use the Supplier Tab, because my situation has too many combinations for your suggestion to be practical. Attached is the file with my modified formula alongside your original formula.

However, your formula works, thank you! Will you provide a more detailed explanation of what your formula is doing, or share some explanatory literature?