Draft day registration help!

I'm in a sports league and we do a draft (not a fantasy draft but very similar) for the teams. A bunch of captains sit in a room and go around picking their players for the season. In our league, we let players rank themselves and they are allowed to also to request one other player they want to play with, which only appears if both players request each other. If you draft one player, you have to draft who they want to play with. However, you cannot draft someone with a lower rank until you've drafted their partner's rank.

Example: The first captain drafts player 11 (rank 10) who wants to play with player 15 (rank 9). So they have officially drafted 11, but not player 15 (who is on their team). They cannot draft another player at rank 9 or below until they draft player 15.

Needs:
I need an excel sheet that can alert the user when a false pick is made (either trying to draft someone already drafted or picking someone before you have cleared the players you've already drafted as partners to others). My first sheet is the data sheet I import from registrations. The second sheet is what I've created so far, which only requires that the user input the ID number of the player into column D and fills in the other values from the first sheet based off that ID number:
=IF(ISBLANK(D2),"",INDIRECT("'" & A2 & "'!" & "C"&D2))

What I'm totally lost with is what to do from here. I'd like to avoid using macros or anything like that if possible. A thought I had was to have a sheet or multiple sheets showing the players on each team. However, I don't know how to search my column on sheet 2, lookup a value (1 for team 1), then return the values in column x, y and z of that row to populate a team list. I figure once I can get there, I can start creating checks, but that's just an idea.

I've looked a little into vlookup, index, match, and nothing seems to work. Any help would be greatly appreciated, and I'll answer any questions I can. Thank you!!!

How many teams? 8
How many players (roughly)? maybe 140ish
How many players does each team draft? Can it be different for different teams if the second number above does not evenly divide the first? no, every team will have 6 women and 8 men
Are players numbered, as in your example? yes, all players are given an ID number
What is the range of player rankings allowed? rankings usually rank from 8 to 18

Like I said at the end of the day I'm just dealing with ID #s and rankings. Some IDs come with another ID that is then on your team, just not yet drafted by you. Need a way to check and make sure the number being input hasn't already been selected and that if an ID # is attached to another, that it doesn't pick that rank or lower before the partner ID # has been picked.

Thank you for any help at all!!

Here is an update with two corrections.

On the Partner sheet, if the player has not requested a teammate, then the formula returned a 0 instead of a blank. This caused problems elsewhere. There were a couple of ways to resolve this but I modified the formula to return a blank instead of a 0.

On the Rank Order Check sheet, the formulas in B3:I18 include a check of the corresponding cell in K3:R18. However, those cells will be 0 if no players were picked after the referenced player, which causes problems in the formulas. So I added a check that the value is >0.