This is a classic case where the right tool for the job is not the one sitting on your desktop. The user here, let's call them the fruit-pairing coordinator, has done the hard part. They have structured the data, identified the constraint (no duplicate fruit assignments), and correctly diagnosed the core problem: manual matching is error-prone and unsustainable at scale. The request for a formula-based solution is understandable, but it is also a trap. Formulas in Excel are wonderful for calculations, lookups, and conditional logic. They are not designed to solve iterative assignment problems with variable constraints, especially when the data set changes size every year.
The practical reality is that this is a matching problem, and Excel's built-in functions will not handle it gracefully. A single formula cannot look at a list of preferences, check every possible pairing, backtrack when a conflict arises, and then try a different combination, all without creating circular references or requiring manual intervention. The user's instinct to try Power Query or VBA is the right one, but even those have limits. Power Query is excellent for transforming and cleaning data, but it does not run iterative optimization. VBA can, but writing a stable macro that handles varying numbers of fruits and individuals, plus preference lists of different lengths, is a serious project, not something a self-described beginner should tackle alone.
What this really calls for is a different approach entirely. The coordinator should look at tools designed for assignment problems, like the Solver add-in that comes with Excel. Solver can handle this type of optimization: maximize the number of successful pairings given a set of binary constraints (each fruit to one person, each person to one fruit, only from their preference list). It is dynamic, can be reconfigured each year, and does not require writing code. The trade-off is that Solver requires learning its interface and understanding how to set up a decision variable table and constraints, but that is a one-time investment that pays off every subsequent year. Alternatively, if the event grows large, a purpose-built tool or a simple script in Python might be faster, but the coordinator explicitly said they are on Windows with Office 2021, so Solver is the most accessible next step.
Our opinion is plain: stop trying to force a square peg into a round hole. The formula approach will fail, and the manual method will cause burnout. The smart move is to spend an afternoon learning Solver or finding a template for assignment problems. That effort will save dozens of hours over the coming years and remove the headache of verifying that no duplicate slipped through. The fruit-pairing coordinator deserves a solution that works as hard as they do, and that solution is not a single formula, it is a tool built for exactly this kind of decision-making.