Need a formula for allocations based on preferences and Priorties

Hello All,

I have a problem wanted to solve using excel formulas. Let me try to explain the problem using an example - There are 5 vacant positions at location A for some X job, 4 vacant position at location B and 3 vacant position at location C. There are 12 candidates for these positions with Rank 1 to 12. Each of 12 candidates has filled preference of working on these locations. However, individual with Rank 1 (lower Rank) would be given preference in order to assign the locations (i.e., if there's only 1 position left at location A and individual of both Rank 1 and Rank 2 filled it as their first preference, then individual with Rank 1 will be priortized and get the position at location A.)

I have attached an excel with sample input and expected result.

I couldn't figure out the excel formula that will do this. It will be great to get your thoughts and help on excel formula with this.

Many thanks in advance!

Hi CAntosh,

The formula is working perfectly. Many thanks for your time and help on this.

Hi FlameRetired,

Thank you so much for taking out time for looking into this and sharing the link. That's really helpful!

Hello!

I was wondering if there was a way for the function to work without the ranking part - i.e. best possible allocation based on preferences.

The rest of the function is very elaborate! Impressive!