I've got this (long) table on Google Sheets:
The cells of column B may be vertically merged, but the number of rows for each multi-cell is random.
Since, for each multi-cell, the top cell is the one containing the value, I need to use a formula which refers, for instance, to the cell B1 in the cells A1 and A2, to the cell B3 in the cells A3, A4 and A5, and so forth.
I thought to build a kind of bottom-up coalesce. E.g. if I'm in cell A10, I want to go from $B10 towards the top until I find a non-null value (i.e. until $B7).
For doing this, I tried to insert the above formula in A10:
=INDEX($B10:$B$1,MATCH(FALSE,ISBLANK($B10:$B$1),FALSE))
but it doesn't accept a range starting from a lower position with respect to the endpoint. Or, better, such range will be automatically flipped to $B$1:$B10. It follows that the first non-empty value is the one of $B$1 and the returned value in A10 is 3 instead of 1.
Is there a way to express such inverted range? In case it isn't possible, how can I solve this problem?
