How to retrieve the last value of a column and print it in the same column in Excel-CodePudding

I have some numerical values in column A like:

ColumnA
15
25
40

What I need is to find the last number under column A and print it under column A as well. When I use the function below and use a different column to print, it works! However, when I use it under column A, it shows 0.

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

Example below:

How can I do it? Considering the example, how can I add a function in column B below 567 and it should show 567 because it is the last number on that column before the function gets used?

Any help would be appreciated.

Regards.

CodePudding user response：

Use INDEX to set the last cell as above the one in which the formula is placed:

=LOOKUP(2,1/(A1:INDEX(A:A,ROW()-1)<>""),A1:INDEX(A:A,ROW()-1))