This is a classic case of a formula that *should* work colliding with a subtle behavior in Excel's calculation engine. The user's intuition is sound, using `WEEKNUM` inside an `INDEX` to build a logical array, then feeding it to `MATCH`, but the error isn't in the logic; it's in how `INDEX` handles that array when it's wrapped in `MATCH`. The `INDEX` function, when used with a zero row number, creates an array of the entire range, but the `WEEKNUM` function inside it is being evaluated in a way that returns a single value, not an array. The formula is trying to compare `WEEKNUM(TODAY())` against a single `WEEKNUM` result, not a list. That is why you get `#VALUE!`: the `MATCH` function cannot find a match because the second argument is not an array of week numbers, it's a single value.
The fix is elegantly simple and speaks to a deeper lesson about array construction in modern Excel. In Office 365, you can replace the `INDEX` wrapper with a direct Boolean comparison using `WEEKNUM(A2:A13)=WEEKNUM(TODAY())`. That alone creates a dynamic array of TRUE/FALSE values. `MATCH(TRUE, WEEKNUM(A2:A13)=WEEKNUM(TODAY()), 0)` will then correctly locate the row of the current week. No helper column, no `CTRL+SHIFT+ENTER`. The user was one small structural choice away from a working solution.
What this reveals is a common friction point for experienced spreadsheet users: the moment when a tool's flexibility becomes its own trap. The user knows the concept, match today's week number, average four rows, but the syntax for generating that comparison array is not intuitive, even for someone who understands `INDEX` and `MATCH` separately. This is exactly the kind of pain point that an AI-native spreadsheet tool can eliminate. Instead of wrestling with why `INDEX` collapses an array, you should be able to say, "average the last four weekly totals based on today's week number," and let the tool handle the structural plumbing.
Our take is this: the user's frustration is legitimate, but the solution is within reach. The formula `=AVERAGE(TAKE(FILTER(B2:B13, WEEKNUM(A2:A13)<=WEEKNUM(TODAY())), 4))` would give you the average of the most recent four weeks, including the current one, without any error-prone array gymnastics. Or, if you prefer the `MATCH` route, drop the `INDEX` and let Excel's dynamic arrays do the work. The real takeaway is that spreadsheet logic has evolved faster than many of its users' mental models. It is time to stop treating formulas like puzzles and start treating them like conversations with your data.