Let's say I've got a table like this

What I want to do is get a formula that always gets the last value in column B and put it on cell D1, so in this case I want to get the value "21" but then if I keep adding values to this list I want to always get the value placed in the last row on that list. is there a way to do this?

CodePudding user response：

Found a way to do this.

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

CodePudding user response：

Sure. Use COUNTA(B:B) to count all the non-empty cells in column B, use ADDRESS() to build the address of the last cell and use INDIRECT() to pull the value of the said address.

or you can use INDEX() to combine indirect and address