This is a problem that should have been solved years ago, and the fact that it still requires merging cells, conditional formatting workarounds, and a cascade of XLOOKUPs tells you everything about how far traditional spreadsheets have fallen behind what teams actually need. The user here has built something functional but fragile, a holiday tracker that works until someone adds a row, changes a name, or the project list grows by one. That's not a tool; it's a maintenance burden.
What this person is asking for is straightforward: show me the holidays for the people on *this* project, and hide the rest. In a modern AI-native spreadsheet, that's a single filter or a dynamic view, not a multi-step formula hunt. The fact that they've tried VLOOKUP, XLOOKUP, MATCH, INDEX, IFS, and ANDs, and still can't get the conditional formatting to override itself, isn't a reflection of their skill. It's a reflection of a tool that makes simple things hard. When your holiday planner requires more logic than your project budget, something is off.
The practical takeaway here is that the spreadsheet itself is the bottleneck. Every merged cell and nested IF statement is time spent fighting the tool instead of managing the team. An AI-native alternative would let you define a person, assign them to a project, and then instantly toggle a view that shows only relevant absences. No conditional formatting battles. No partial-match columns. No "y" or "n" workarounds. The system should understand the relationship between people, projects, and time off without you having to rebuild it every quarter.
So here is our plain opinion: stop patching the old guard. This holiday tracker is a symptom of a larger problem, spreadsheets that treat data like a canvas instead of a structure. The solution isn't a better formula. It's a smarter tool that lets you ask "who's out on Project Delta next week?" and get the answer in one click, not one afternoon of debugging. That future is available now, and it doesn't require you to merge a single cell.