Toppers wrote...
=LOOKUP(10^99,A:A)

will return last value in column A
....

Picky: it'll return the last numeric value in the column, but it won't
return any, text, boolean or error value that might come after the
last numeric value. To return the last value regardless of type,

=IF(COUNT(A65536),A65536,LOOKUP(2,1/NOT(ISBLANK(A1:A65535)),A1:A65535))