Bidding Schedule

[Richie192]

I'm trying to come up with a formula that awards a schedule to employees. The employees are ordered by seniority. There are multiple openings per periods. The employees bid for the period they will work based on seniority and list them by their 1st through 5th choice.

I found a solution here: https://www.mrexcel.com/forum/excel-questions/872573-shift-bid-schedule-question.html
However, that solution has the ranking for the bids listed differently.

How can I modify the formula to give me the final award?

[TABLE="class: grid, width: 800, align: left"]
<tbody>[TR]
[TD]Period[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Openings[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee
[/TD]
[TD]1st Choice[/TD]
[TD]2nd Choice[/TD]
[TD]3rd Choice[/TD]
[TD]4th Choice[/TD]
[TD]5th Choice[/TD]
[TD]Final Award[/TD]
[/TR]
[TR]
[TD]Andy[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blake[/TD]
[TD]E[/TD]
[TD]D[/TD]
[TD]C[/TD]
[TD]B[/TD]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Charles[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]A[/TD]
[TD]E[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Damien[/TD]
[TD]D[/TD]
[TD]C[/TD]
[TD]B[/TD]
[TD]E[/TD]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Earl[/TD]
[TD]E[/TD]
[TD]C[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Frank[/TD]
[TD]B[/TD]
[TD]D[/TD]
[TD]A[/TD]
[TD]C[/TD]
[TD]E[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]E[/TD]
[TD]C[/TD]
[TD]A[/TD]
[TD]D[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Holly[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]E[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Irma[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]D[/TD]
[TD]C[/TD]
[TD]E[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]