This is a clever problem that reveals a fundamental limitation of traditional spreadsheet logic. You're asking Excel to do something it was never designed to do: think sequentially, remember what it already found, and adapt its next output accordingly. The fact that you're trying to solve this with a row of identical XLOOKUP formulas tells us you've already hit the wall that millions of spreadsheet users eventually hit. The tool expects every cell to operate independently, but your task requires a chain of dependent decisions. That gap is exactly where productivity stalls.
The core issue here is not your formula syntax. It's that Excel's native functions are stateless. Each XLOOKUP evaluates the same source list without any memory of what the formula to its left already returned. So when the first cell finds "Charlie," the second cell has no way of knowing that "Charlie" is no longer available. You end up with duplicates, manual workarounds, or a mess of helper columns that turn a simple request into a fragile web of references. This is not a reflection of your skill. It is a reflection of the tool's architecture.
What you really need is a system that understands context. A modern, AI-native approach would let you describe the outcome you want, "give me five unique names from this list", and handle the filtering, deduplication, and ordering automatically. Instead of writing five formulas that fight each other, you would write one intent. The tool would recognize that each output depends on the previous one and would manage that dependency behind the scenes. That is the difference between instructing a calculator and collaborating with an intelligent assistant.
For now, the practical solution involves either using the UNIQUE function in combination with INDEX and SEQUENCE, or accepting that a single dynamic array formula is the right tool for this job. But the deeper takeaway is this: when your workflow forces you to fight the logic of your spreadsheet, it is worth asking whether the spreadsheet itself is the right tool. The future of data work is not about mastering workarounds. It is about tools that understand what you mean, not just what you type.