Function to find value in cell

[medwards]

Is there a function or a formula out there that will return the value of
the last non-blank cell in a column?

 

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

 

[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

 

[Peo Sjoblom]

Note that the first formula only works for numeric values

 

[Ken Wright]

Cheers Peo

 

[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.

 

[Ken Wright]

Aladin, I may have said this before, but You Da Man!!!

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

 

[Harlan Grove]

=LOOKUP(REPT("z",255),A:A)

LOOKUP(CHAR(255),A:A) would be more robust.

 

[Aladin Akyurek]

...

LOOKUP(CHAR(255),A:A) would be more robust.

It fails on...

{"dra";"fra";"";"z";"zzz";"zodiac"}

where "" stands for a formula-generated blank.

 

[medwards]

Those formulas you supplied work great! Thanks for your help Ken and
Peo.

 

[Harlan Grove]

It fails on...

{"dra";"fra";"";"z";"zzz";"zodiac"}

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.