We are looking at a user who has built a working solution in Excel, a formula that returns a column number based on a dynamic result, then uses that column offset to pull a matching value. It is clever, functional, and exactly the kind of workaround that defines spreadsheet expertise. But it is also fragile, hard to read, and difficult to maintain. The formula, built with LET, INDEX, OFFSET, and MATCH, works because the user understands the logic. It does not work because the tool makes the logic visible or reusable. That is the fundamental gap this story exposes: legacy spreadsheets reward cleverness with complexity, while modern tools reward clarity with simplicity.
Power Query can handle this exact task, and it can do so in a way that a colleague, or your future self, can open six months later and understand immediately. Instead of nesting functions inside a single cell, you would unpivot the source table, filter to the relevant column based on the Result value, and merge back to the Reference. The operation becomes a sequence of transparent steps, each one visible in the applied steps pane. No OFFSET to break when a row shifts. No hard-to-debug column offset. The transformation is explicit, auditable, and repeatable with a single refresh. That is not just a technical improvement; it is a shift in how you relate to your data.
The user's question is practical, and it deserves a practical answer. Yes, there is a way to do this in Power Query. More importantly, the approach unlocks a pattern: dynamic column selection based on row values is a common need in messy, real-world datasets. Whether you are pulling quarterly budgets from a pivoted table or extracting product attributes from a wide header row, the same logic applies. Power Query handles it with Table.UnpivotOtherColumns and Table.SelectRows, or with custom M functions that are far more readable than the original formula. The user already has the logic figured out. Now they need the right tool to execute it without the overhead.
Our opinion is plain: this formula is a sign that the user is ready for Power Query. It is not a criticism of the user's skill, quite the opposite. They have outgrown the constraints of cell-based formulas. The next step is to embrace a tool that treats data as a flow, not a fixed grid. For anyone reading this who has written a similar workaround, consider this an invitation. Open Power Query, load your source table, and try unpivoting the column structure. You will likely find that the solution you built with effort in Excel becomes a few clicks and a single M expression. That is the promise of AI-native data tools: not replacing your expertise, but removing the friction between your intent and the result.