**Our Take**
This user's frustration is completely understandable, and the error message is a classic trap that trips up even experienced spreadsheet users. The formula *looks* right, FILTER should absolutely work here, but the problem is a mismatch in array sizes. The `FILTER` function expects a single-column condition that matches the height of the array you're filtering, not a condition written against the entire column from the source sheet. When you write `('Sheet 1'!E4:E1048576>0)`, that's a 1,048,573-row array, but your main data range `A4:AD1048576` is also that many rows, so logically it should match. The real issue is that `CHOOSECOLS` then tries to pick column 5 from a filtered array that may have fewer rows than the original, and `UNIQUE` gets confused by the mismatch in how the filter condition is evaluated across the entire sheet vs. the actual used range.
In practical terms, the fix is to define explicit, equal-sized ranges for both the data and the filter condition. Instead of referencing full columns, write: `UNIQUE(CHOOSECOLS(FILTER('Sheet 1'!A4:AD1000, 'Sheet 1'!E4:E1000>0), 5))`. Replace `1000` with the actual last row of your data. This tells the formula exactly where to look and stops it from trying to evaluate empty cells. If your dataset truly spans to row 1,048,576, consider using a dynamic named range or a table reference, most AI-native spreadsheets handle tables far better than open-ended ranges, and they keep your formulas clean when data grows.
What this really highlights is that even modern spreadsheet tools still carry legacy quirks from their desktop ancestors. The user's instinct to nest FILTER inside UNIQUE is smart, it's exactly the kind of layered logic that makes AI-powered spreadsheets so much more capable. But the tool should make that instinct work, not punish it with a cryptic error. The good news is that once you understand the range-limitation pattern, it becomes predictable and easy to avoid. And for anyone building complex workflows, this is exactly the moment to explore a spreadsheet that handles array math natively, without forcing you to count rows.
So here's the concrete takeaway: always match your filter condition's range to your data range, row for row. Use tables or dynamic ranges to future-proof your formulas. And if you find yourself fighting range-mismatch errors more than once a week, it's worth asking whether your spreadsheet is helping you solve problems, or just creating new ones.