Need a formula to determine priority based on rank

Hello All,

I have not exact but a similar problem. Let me try to explain taking 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!