Find Last Cell Value

Assuming range starts from cell A1 downwards.

=INDEX(A:A,COUNTA(A:A))

(This does not work with blanks)

For Text

=INDEX(A:A,MATCH(REPT("z",255),A:A))

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

For Numbers

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

=LOOKUP(9.99999999999999E+307,A:A)

(You can probably use a smaller number than 9.99999999999999E+307, but make sure it is bigger than any number you might use in your file)

For Text or Numbers

This is a way that I thought up (but I may not be the first)

In B1 enter =IF(A1="","",ROW()) and drag down

Then in C1 enter =INDEX(A:A,MAX(B:B))

or

For a range such as A1:A100, use this formula (it will work with entire rows but not entire columns, change ROW to COLUMN for working with rows)

{=INDEX(A1:A100,MAX(NOT(ISBLANK(A1:A100))*ROW(A1:A100)))}

Must be entered as an array. (Push Ctrl, Shift and Enter simultaneously)