This user's problem is exactly the kind of friction that keeps smart people trapped in legacy tools. They have a working solution, nested IF statements, ISNUMBER, XMATCH, XLOOKUP, but it feels slow, clunky, and unrefined. They are right to be frustrated. When a formula becomes a maintenance burden because of file paths you cannot change and logic that sprawls across multiple helper cells, the tool is no longer serving you. You are serving the tool.
The core challenge here is not the complexity of the lookup logic. It is the rigidity of the environment. The user needs to check a reference code in either Column A or Column B of a second workbook, then pull five columns of data based on that match. That is a straightforward conditional lookup. In a traditional spreadsheet, the only path is to build a tower of conditional logic that checks one column, then the other, then returns "Not Found" if neither works. The formula grows exponentially with each additional column you need to pull. And because the file names and locations are fixed, you cannot simplify the references. The result is a formula that is technically correct but practically brittle.
What this user really needs is a system that treats the lookup as a single operation, not a nested decision tree. An AI-native spreadsheet would allow you to describe the logic in plain language: "Find the EntID in Workbook 2, checking Column A first and then Column B. If found in either, return the values from these five columns. If not, show 'Not Found'." The software would handle the conditional branching and file-path management internally. The user would see a clean, readable formula, or even no formula at all, just a result that updates as data changes.
This is not about replacing the user's skill. Their nested formula is evidence of deep competence. It is about removing the unnecessary friction that comes from tools designed for a world before cross-workbook, multi-column lookups were common. The practical takeaway is this: if you find yourself writing a formula that feels like a workaround, it probably is. The best tool is the one that lets you express what you need, not the one that forces you to engineer around its limitations. The user should not have to be a formula architect to get five columns of data from another file. They should be able to ask for it.