How to get the next available if one is already taken.

**vs27739** · 05-08-2018, 06:24 PM

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.

**FDibbins** · 05-08-2018, 06:45 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**Richard Buttrey** · 05-08-2018, 06:45 PM

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.