This approach works, but it is also the kind of fix that reveals the limits of the tool you are using. The formulas are clever, FILTER, SORTBY, RANDARRAY, XMATCH, and they show a genuine understanding of how to make a legacy spreadsheet perform tricks it was never designed for. But look at what it costs you. Every cell in columns C and D needs its own variation of the formula, each one checking a different range to avoid duplication. That works for a week. Scaling it to a full year means either a monumental manual effort or a spreadsheet so fragile that one typo breaks everything.
The practical problem here is not your logic. It is the medium. A spreadsheet is a static grid. You are trying to make it behave like a dynamic scheduling system, which means you are spending your time building guardrails instead of managing your team. The leave register already tracks availability, and you have successfully pulled that data into a randomized list. The hard part, knowing who is free, is solved. The rest is just accounting for who has already been assigned, which is exactly the kind of repetitive, error-prone work that a purpose-built tool handles automatically.
What you are doing is admirable, and it works for now. But it is also a sign that you have outgrown the spreadsheet. The moment you find yourself writing a formula that checks a range that grows with every cell you add, you are no longer automating a roster. You are maintaining a manual system that happens to use formulas. The real value is not in making the spreadsheet smarter, it is in moving the logic out of the cells and into a system that understands context, remembers assignments, and does not require you to rebuild the range reference every time you add a new column. That shift is what frees you from the spreadsheet and lets you focus on the roster itself.