There's a moment every spreadsheet user knows well: the formula that works perfectly in isolation, then falls apart the moment it meets the real world. That's exactly what happened here, and the frustration is entirely justified. The user built a clever nested XLOOKUP, got it working on a single row, and then watched it return an error when applied across the full dataset. The instinct was right, but the approach was missing one crucial piece: the search mode argument doesn't behave the way it seems when you're working with a two-dimensional range instead of a single row.
What's actually happening is subtle but important. When XLOOKUP searches a row, the `-1` search mode works as expected, scanning from right to left. But when you point it at a full block of data, the function interprets that range differently. It's not scanning each row independently; it's treating the entire array as one continuous stream. That's why the formula breaks: the logic that works for one row doesn't scale to many rows, because the lookup array's orientation and the search mode's intent get tangled up in the multiplication of conditions. The user didn't miss a detail; they hit a structural limitation of the tool.
The practical takeaway is that this kind of problem needs a different mental model. Instead of trying to force a single XLOOKUP to do everything, the solution lies in isolating the relevant row and column first, then performing the right-to-left search on that narrowed range. Something like using INDEX and MATCH to lock onto the correct year row, then applying XLOOKUP across just that row with the `-1` search mode. That two-step approach respects how the functions actually operate, rather than asking one formula to override the fundamental structure of the data.
This isn't a failure of effort or intelligence. It's a reminder that spreadsheets reward precision in how you frame the problem, not just in which functions you choose. The user was close, and with a small shift in strategy, they'll get the result they're after. The fix is within reach, and it's worth the few extra minutes to understand why the original formula stumbled. Because once you see the pattern, it stops being a frustrating mystery and becomes a tool you can reach for again.