example 1
I have a column of data
last entered data is in C4
C5 and C6 are blank
C7 has a Lookup formula

example 2
when data is added, a row is inserted below C4
new data is entered in C5
C6 and 7 are blank
C8 has the lookup

etc

I'm trying to figure out a formula I can put into
another cell, say A1, that would =
in example 1 = C3
in example 2 = C4
(the row above the last entered data)
(not the row above the last nonblank cell(the lookup formula)

in other words, in example 1, A1.Value = C3.Value
in example 2, A1 changes to = C4.Value

is there a way to do this with builtin excel formula?
Thanks
mark