CODEWITHSUNDEEP
excelexcel-formulaexcel-2016
Eray Balkanli
Eray Balkanli

Reputation: 8000

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

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:

enter image description here

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.

Upvotes: 0

Views: 179

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

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))

enter image description here

Upvotes: 1

Related Questions