Use the value in the last non-empty cell without using VBA

I have a simple table:

I want to use the value in the last filled "k" row (currently in this example it would be the 8, but the week prior it would have been the 2, and next week it would be whatever is entered into the 6th) in a calculation.

Is there a way of doing this formulaically rather than using the dreaded VBA?

>Solution :

Try using either of the one:

• Formula used in cell M2

=LOOKUP(2,1/(K:K<>""),K:K)

• Formula used in cell N2

=INDEX(K:K,MATCH(7^89,K:K))

• Formula used in cell O2

=XLOOKUP(TRUE,K:K<>"",K:K,,1,-1)

Here is an update please see it works

The solutions provided is working as per requirements