How to refer to a merged cell in a formula (or to a "bottom-up" range)-CodePudding

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?

CodePudding user response：

You can fill column C with values

=ArrayFormula(lookup(row(B1:B),row(B1:B)/if(B1:B<>"",1,0),B1:B))

or, for one specific row, you can retrieve the last value by

=ArrayFormula(lookup(row(),row(B$1:B)/if(B$1:B<>"",1,0),B$1:B))

CodePudding user response：

in A10 or any other row you can use:

=INDEX(VLOOKUP(ROW(), IF(B:B<>"", {ROW(B:B), B:B}), 2))

if you want to fill the whole A column with B values use this in A1:

=INDEX(VLOOKUP(ROW(B:B), IF(B:B<>"", {ROW(B:B), B:B}), 2))