This is a frustrating bug, and it points to a real gap in how many users understand Excel's new calculation engine. The problem isn't your formula logic, it's the mismatch between what the formula evaluator shows and what the grid actually renders. That gap is the story here.
Your INDEX/MATCH is structurally sound. The evaluator confirms it. But the spilled result in D3 and D4 stubbornly mirrors D2. This is a known behavior when hash operators interact with implicit intersection in a workbook hosted on SharePoint, especially with live co-authoring. The engine sometimes resolves the spilled reference correctly during evaluation but fails to re-evaluate the output range after the initial spill. It's not an error in your syntax; it's a recalculation order issue. The formula evaluator operates in a sandbox, running the calculation step-by-step without the full context of the workbook's dependency tree. The grid, on the other hand, applies the result of the first cell to the entire spill range if the engine decides the formula is "stable" and doesn't need per-row re-evaluation. That's what you're seeing.
For practical work, this means you cannot trust the evaluator alone when dealing with spilled arrays and hash operators. The tool is a debugger, not a validator of final output. Your workaround, forcing the spill with `=IF(B2#="",""...)`, is clever, but it masks the issue rather than solving it. A more reliable approach is to avoid hash operators inside INDEX/MATCH entirely when you need per-row accuracy. Use XLOOKUP with a concatenated helper column, or switch to a `LET` function that explicitly defines the lookup array before the match. Both methods force the engine to treat each row as an independent calculation, bypassing the stale-spill behavior. You can also test by opening the workbook in desktop Excel with co-authoring disabled, if the results correct themselves, the problem is tied to SharePoint's sync layer, not your formula.
This isn't a reason to abandon spilled arrays. They are genuinely transformative for reducing manual drag-and-fill work. But they demand a new mental model: the formula is no longer a cell-level instruction; it's a range-level instruction. When you combine that with INDEX/MATCH, you are asking the engine to resolve a two-dimensional lookup against a one-dimensional spill, and the engine's shortcuts sometimes trip over themselves. The fix is to match the tool to the task, use hash operators for simple expansion and aggregation, and reserve INDEX/MATCH for cases where you need explicit, row-independent accuracy. Your diagnosis was right; now the solution is about choosing the right tool for the job, not fixing the tool you already chose.