Hello everyone, after looking left and right without luck, I figured I should just register and see if someone can help me out. I consider myself a fairly advanced user, but this one is really killing me.

Assume you have 2 tables both have names as row heading, and product name as column heading.

In each cell of Table #1 you have the maximum amount that each individual wants of each product.
Here's a dummy example
New Picture.jpg


In each cell of Table #2 you have the the cardinal priority of each product (lets say you have product A, B and C; if individual X puts in 2 3 1 it means he wants product C as #1 priority, product A as #2 and product B as #3).
Here's a dummy example:
New Picture (1).jpg

Now assume that you have a constraint to the maximum number of product that you can deliver:so you prorate the total decided by each individual and then allocate it across their selection based on the ranking.

In the examples above, let's say that only 700 products are available so the proration (700/1330) is 53%. Individual X wants 450, but he gets 53%, which is 238: based on the prioritazation all 238 are product C. Individual Y wants 455, but only gets 241, based on the prioritization he gets 30 product B and 211 product C.

Of course this is a simplified case, but I hope you get the logic. In reality I have about 1200 individual and 35 products, and I would like to use an array function for various reason.

I have not been able to come up with one yet and for now I have a workaround that uses SUMIF and Circular Reference... Can anyone help?