M
medwards
Is there a function or a formula out there that will return the value of
the last non-blank cell in a column?
the last non-blank cell in a column?
K
Ken Wright
If you just want the last value in the column, regardless of whether or not you have any blanks in
your data, then the following will both do it:-
=LOOKUP(9.99999999999999E+307,A:A)
=INDIRECT("A"&SUMPRODUCT(MAX((ROW(A1:A1000))*(A1:A1000<>""))))
your data, then the following will both do it:-
=LOOKUP(9.99999999999999E+307,A:A)
=INDIRECT("A"&SUMPRODUCT(MAX((ROW(A1:A1000))*(A1:A1000<>""))))
P
Peo Sjoblom
= OFFSET(A1,COUNTA(A:A)-1,0)
or if there are blanks within the nonblanks
=INDEX(A:A,MAX((A1:A1000<>"")*ROW(INDIRECT("1:1000"))))
the latter entered with ctrl + shift & enter
or if there are blanks within the nonblanks
=INDEX(A:A,MAX((A1:A1000<>"")*ROW(INDIRECT("1:1000"))))
the latter entered with ctrl + shift & enter
P
Peo Sjoblom
Note that the first formula only works for numeric values
K
Ken Wright
Cheers Peo 
A
Aladin Akyurek
=LOOKUP(REPT("z",255),A:A)
if A is text or one is interested in the last text value.
If A might house any value, including logicals and/or error values, a
different (expensive) formula is needed.
if A is text or one is interested in the last text value.
If A might house any value, including logicals and/or error values, a
different (expensive) formula is needed.
K
Ken Wright
Aladin, I may have said this before, but You Da Man!!!
H
Harlan Grove
LOOKUP(CHAR(255),A:A) would be more robust.
A
Aladin Akyurek
Harlan Grove said:
It fails on...
{"dra";"fra";"";"z";"zzz";"zodiac"}
where "" stands for a formula-generated blank.
M
medwards
Those formulas you supplied work great! Thanks for your help Ken and
Peo.
Peo.
H
Harlan Grove
You're right. I was just assuming that text would work the same way as numbers,
but it appears LOOKUP with text values uses some sort of collation sequence that
includes only char codes between 32 and 126 and makes z come last.