Retrieving last actual value in a list

[jonny]

I am trying to get excel to identify the last numerical value in a column,
but depending on data fed in the last value may be in a different row wihtn
a column. For example in one instance the last value may be A3. In another
A5, in another A6 etc. Does anyone know the formula that I would put in
another cell to return the value of the last cell in column A that actually
has a value. Many thanks in advance.

Jonny

 

[Mike H]

Hi,

You don't say wheter you want the value or the row so:-

Value
=LOOKUP(10^10,A:A)

Row
=MATCH(10^10,A:A)

Mike

 

[FSt1]

hi
=OFFSET(A2,COUNTIF(A2:A100,">0")-1,0)

Regards
FSt1

 

[jonny]

Many thanks - works perfectly

hi
=OFFSET(A2,COUNTIF(A2:A100,">0")-1,0)

Regards
FSt1

 

[jonny]

THank you for your advice

Hi,

You don't say wheter you want the value or the row so:-

Value
=LOOKUP(10^10,A:A)

Row
=MATCH(10^10,A:A)

Mike

 

[Excel_Learner]

If value is no. then
=index(A:A, match(999.9999999, A:A, 1)) and value is text
=index(A:A, match("zzzzzzzzzzzzzzzzzzzzzzz", A:A, 1)
Kindly let me know if these work.