Excel Formula – string multiple logical steps together

Background: I want to generate a schedule from a list of all employees that is filtered for conflicts, specifically listed in one of two "Vacation" cells or they have already been scheduled in a conflicting role that same week.

  1. List of Employees: A to J (Cells E1:E10)
  2. Vacation: (Cells V1,V2) for instance, employee C and F
  3. Job 1: (Cell J1) Employee D
  4. Job 2: (Cell J2) Employee A
  5. Job 3: Here I want all available employees of A to M with any names matching cells V1, V2,J1 or J2 excluded. This would show me which employees are available to schedule in Job 3 (in this case B, E, G-J)

Make sense?

I can work with a drop down list of all eligible employees OR I can have a specific cell for each employee A through M showing "No" or "Yes" for availability if easier.

Thanks in advance! It’s embarrassing how long I have been trying to IF and AND my way through this