"g-boy" wrote...
....
The problem is that ROW() always returns an array, even when the array
only contains a single entry.

I didn't know that... I just assumed it returned the single (scalar) value
that was the row number of the cell in which the formula appears.
....

When formulas don't work when you change from a hardcoded value to a
function call, you should always try evaluating the particular function call
in the formula bar. If you type =ROW() in, say, X99 and press [F9], it
becomes ={99}.

This is just another of those things that you have to learn by experience
because online help for the ROW() function certainly doesn't mention this.
Indeed, I suspect ROW() [and COLUMN()] returning single entry arrays is a
bug, but Microsoft doesn't waste money fixing bugs that would affect only
the 10% or fewer of Excel users trying to do things like this.