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

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
"jonny" wrote:

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

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

Regards
FSt1

"jonny" wrote:

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

Many thanks - works perfectly

"FSt1" wrote:

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

Regards
FSt1

"jonny" wrote:

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

THank you for your advice

"Mike H" wrote:

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
"jonny" wrote:

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

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.

"jonny" wrote:

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