The user who posted this question has identified a real performance bottleneck, and their instinct to keep data out of the workbook is exactly right. Loading every intermediate query into worksheet cells creates unnecessary bloat, forcing Excel to recalculate and refresh layers of cached data that you never actually need to see. The solution is straightforward, and it does not require abandoning formulas like XLOOKUP.
Power Query allows you to set queries to "Connection Only" mode. When you do this, the query runs and stores its result in Excel's data model, but it does not populate a worksheet table. The data remains available for other queries to reference, and you can still pull specific values into cells using DAX formulas or the CUBEVALUE function, which behaves much like XLOOKUP but reads from the data model instead of a visible range. This approach drastically reduces workbook file size and calculation overhead because Excel is no longer rendering every intermediate dataset on a sheet.
What this means in practical terms is that you can build your entire query stack, mimicking an ERP system's logic, without ever dumping a single intermediate table into a worksheet. Only the final output that users need to see or interact with should be loaded. The rest stays in the data model, invisible but fully functional. The performance gain comes from eliminating the rendering and recalculation cost of those intermediate tables, which is exactly where the "accreditation of queries on top of queries" creates drag.
The user should test this by taking one of their middle-layer queries, right-clicking it in the Queries & Connections pane, and selecting "Load To" → "Only Create Connection." Then, in the worksheet where they need to reference that data, they can use CUBEVALUE with the appropriate measure or field. It is a small change that yields disproportionate results. The goal is not to eliminate formulas or abandon the worksheet interface. It is to stop treating every query result as a visible table and start treating the workbook as a thin presentation layer over a lean, fast data engine.