How to find the last value in a column when the column is populated from an ARRAYFORMULA

Question

Some columns in my sheet are filled manually, but then two columns are created dynamically from an ARRAYFORMULA (i.e. there is nothing in the column if the manually populated cells are empty, but once they have some data, the other columns get their values populated automatically). To find the last value in a manually populated column, I can use a function like this:

=INDEX(H:H,COUNTA(H:H),1)

But if I use the same formula (adjusted for the right column) on the ARRAYFORMULA column, I don't get an error, but I also don't get any results.

I also tried to do the same with this:

=INDEX( FILTER( H:H , NOT( ISBLANK( H:H ) ) ) , ROWS( FILTER( H:H , NOT( ISBLANK( H:H ) ) ) ) )

This again works fine on a column that is manually populated, but those calculated from an array formula do not populate

In case it is useful, my array formula in I2 is this:

=ARRAYFORMULA(IF(B2:B, B2:B-H2:H, ""))

How do I find the last value in column I?

Answer 1

• change your formula in I2 to:

  =ARRAYFORMULA(IF(B2:B, B2:B-H2:H, ))

• then use this where you need to get last value:

  =QUERY(I2:I, "where I is not null offset "&COUNTA(I2:I)-1, 0)