How to get index match to pick the next available, it keeps picking the same person.

A2:A37 has a list of military ranks.
C2:C37 has a list of personnel with their respective ranks in A2:A37 beside them.

N2:N20 has a list of required ranks needed for each position.
O2:O20 will has the formula in each cell that should pick any personnel in C2:C37 that has required rank and with the function to skip over to the next available personnel with the right required rank in case any personnel are already in one of the cells in O2:O37. If required rank isn't available, it should pick the next person with the closest rank.

Problem: It keeps picking the same person for two positions that have the same rank requirement.

What I want it to do: I want it to pick personnel from C2:C37 that meets the rank requirement, but I'd like it to find another one that hasn't already been taken. If all the personnel with that required rank are taken, then I like it go down one rank requirement and substitute it in for the higher rank. If all none are available, then I'd like it to say "VACANT".

In others, if there are two Captain and three lieutenants, and we have three Captain requirements that need to be billeted, then the two Captains names should be used for the first two and then pick one of the lower ranking lieutenants as substitute for the Captain position. A Major should be replaced by a Captain, and if a Captain is not available, then pick a Lieutenant.

The goal is fill each billet as much as possible.

So far, I've written this formula in O2:

=INDEX(C2:C37,MATCH(N2,A2:A37,0))

How can I improve this formula if I want the next cell O3 to find the next match that wasn't already taken in O2 or any other ones. Essentially, no repeats of personnel, just find the next one if the requirement is met.

Try this with a helper column

In Q2 use this formula and copy down

Formula:

Please Login or Register  to view this content.

Use in O2 and copy down

Formula:

Please Login or Register  to view this content.

See the file