camruny
camruny

Reputation: 29

Get value of cell above last cell in column

I am looking for a formula that will get the value of the cell above the last cell in a column in Excel. I have the following formula that will get the value of the last cell in the column:

=LOOKUP(2, 1/('Historical Data'!A:A<>""),'Historical Data'!A:A)

But I am looking for the value of the cell that is right above it.

For example, if I have a table that looks like:

A           B      C    
2013     09     $40    
2014     10     $78
2015     02     $60

I'm looking for column A to return "2014", not "2015" as it does now.

Upvotes: 0

Views: 1525

Answers (2)

deepakkt
deepakkt

Reputation: 82

Setup a meta row.

For example, in column "D", adjacent to each cell issue the following formula.

=ROW()

Like so,

A           B      C    D
2013       09     $40    1
2014       10     $78    2
2015       02     $60    3

At the end of the last row, issue a MAX command in the row column. In the aboe example, it will be D4.

=MAX(D1:D3)

D4 will have a value of 3

This tells that your range has 3 rows. You may then get your cell value by

=INDIRECT("A" & D4-1)

Which will give you the value of A2.

You may hide row D after everything checks out.

Upvotes: 0

Michal Schmitt
Michal Schmitt

Reputation: 216

To return the second to last value I would use INDEX.

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

COUNTA to get the length of your array and -1 to step to the second to last value.

Upvotes: 2

Related Questions