This is a deceptively smart question, and it exposes a critical gap in how many of us think about dynamic arrays in Excel. The user here, an experienced spreadsheet professional, has correctly identified that a traditional SUMPRODUCT formula works but requires manual dragging. They then attempted to modernize it with BYROW and got stuck. The core issue is not a failure of BYROW, it is a mismatch in how the formula interprets the data structure.
The user's working formula uses `$P$2#` and `$Q$2#`, spilled ranges. When you drag a traditional formula down, Excel implicitly iterates over each row. But BYROW expects to receive a single row or value per iteration from the array you pass it. The problem is that the XLOOKUP inside the LAMBDA is returning a reference to the entire spilled range for each row, not a single value. The SUMPRODUCT then multiplies a vector by a vector, producing an unexpected result. The fix is straightforward: within the LAMBDA, you must force XLOOKUP to return a single value by wrapping it with a function like `INDEX` or by using `@` to return the intersection. A corrected formula would be: `=BYROW(CHOOSECOLS(A2#,1), LAMBDA(x, SUMPRODUCT($P$2# * INDEX(XLOOKUP(x, $Q$1#, $Q$2#), 0))))`.
What this really tells us is that dynamic arrays are not a simple drop-in replacement for legacy formulas. They demand a different mental model. The user is not alone in this frustration. Many experienced users who mastered the old paradigm find themselves tripped up by how array functions scope their calculations. The strength of BYROW is that it makes your spreadsheet truly dynamic, no more dragging, no more broken ranges when new data arrives. But that power comes with a requirement: you must think in terms of row-level operations, not range-level ones. The solution here is not about memorizing a workaround. It is about learning to see your data as a collection of individual records that BYROW processes one at a time. Once that clicks, you will stop fighting the tool and start building spreadsheets that adapt to your data, not the other way around.