Get the last non-empty cell in a column in Google Sheets

Question

There may be a more eloquent way, but this is the way I came up with:

The function to find the last populated cell in a column is:

=INDEX( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ; ROWS( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ) )

So if you combine it with your current function it would look like this:

=DAYS360(A2,INDEX( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ; ROWS( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ) ))

To find the last non-empty cell you can use INDEX and MATCH functions like this:

=DAYS360(A2; INDEX(A:A; MATCH(99^99;A:A; 1)))

I think this is a little bit faster and easier.

If A2:A contains dates contiguously then INDEX(A2:A,COUNT(A2:A)) will return the last date. The final formula is

=DAYS360(A2,INDEX(A2:A,COUNT(A2:A)))

My favorite is:

=INDEX(A2:A,COUNTA(A2:A),1)

So, for the OP's need:

=DAYS360(A2,INDEX(A2:A,COUNTA(A2:A),1))

Although the question is already answered, there is an eloquent way to do it.

Use just the column name to denote last non-empty row of that column.

For example:

If your data is in A1:A100 and you want to be able to add some more data to column A, say it can be A1:A105 or even A1:A1234 later, you can use this range:

A1:A

So to get last non-empty value in a range, we will use 2 functions:

• COUNTA
• INDEX

The answer is =INDEX(B3:B,COUNTA(B3:B)).

Here is the explanation:

COUNTA(range) returns number of values in a range, we can use this to get the count of rows.

INDEX(range, row, col) returns the value in a range at position row and col (col=1 if not specified)

Examples:

INDEX(A1:C5,1,1) = A1
INDEX(A1:C5,1) = A1 # implicitly states that col = 1
INDEX(A1:C5,1,2) = A2
INDEX(A1:C5,2,1) = B1
INDEX(A1:C5,2,2) = B2
INDEX(A1:C5,3,1) = C1
INDEX(A1:C5,3,2) = C2

For the picture above, our range will be B3:B. So we will count how many values are there in range B3:B by COUNTA(B3:B) first. In the left side, it will produce 8 since there are 8 values while it will produce 9 in the right side. We also know that the last value is in the 1st column of the range B3:B so the col parameter of INDEX must be 1 and the row parameter should be COUNTA(B3:B).

PS: please upvote @bloodymurderlive's answer since he wrote it first, I'm just explaining it here.