Excel - Automatic allocation of preferences using IF/AND/INDEX/MATCH?

I'm not a super experienced Excel user, but I'm trying to automate using IF/AND/INDEX/MATCH to assign yes/no to preferences which competitively listed people have chosen from a list of about 300 choices. Each person has listed 4 preferences, there's over 1000 people in the process, and each person gets one preference out of four. A simplified hypothetical version of it is a situation where I want automate the allocation of employee shifts at a restaurant, with the employee data listed with the best workers at the top and the worst ones at the bottom.

I have a workbook with one Shifts Available sheet that lists days of the week in column A, then in column K I have the number of employees I need to work on that day (or available shift places). The number could be between 99 and 0. This feeds into the Shift Allocation sheet, which also pulls its employee and preference data from another Employee Data sheet, which is a data dump from the system where the employees listed their preferences.

Traditionally the Shift Allocation sheet has been done manually, which is fine when you have seven days of the week and maybe 30 employees. But if I have 300 days of the week and 1000 employees, I don't want to manually allocate each one to their preferences. Or if I do have a manual input, I want it to automate enough shoft allocation beforehand so it doesn't take up as much time.

When someone in the Shift Allocation sheet has one of their four preferences allocated to them, this reduces the number of shifts available against that day in the availability sheet by 1, until there's none left.

So ideally, the automated formula to assign the yes/no indicator for the shift in the Shift Allocation would look at the first preference (day of the week) the person has listed in the Employee Data sheet, check the Shifts Available sheet to find the day of the week and then look at the number of shifts available in column K, if column K is more than 0, assign YES to that employee's preference on the Shift Allocation sheet, then move on to the next employee.

If the first preference has no more shifts available, then it would move on to the second shift preference, and so on. The employees are listed by who gets first pick, so in theory some people at the bottom may not get the shifts they preferenced, or there may need to be other manual changes made after the automatic allocation process for other reasons.

I tried VLOOKUP but the lookup logic wasn't broad enough, so I need something like an INDEX/MATCH parameter to find the shift places available in column K of the Shifts Available sheet after finding the day of the week in Column A.

This is about as far as I got with the code

=IF(AND(INDEX('Shifts Available'!$A$2:$K$8, MATCH('Employee Data'!$A$2,0)), 'Shifts Available'![K-column cell corresponding to the day of the week, unsure how to do this? Keep it in the INDEX?]) >=0)"Yes", "No")

Any help is appreciated.