Rick Rothstein wrote :
Assuming the only blank cells in Column C are the two you told us about and
assuming your Lookup formula never returns the empty string (""), then I
think you can use this simple formula...

=INDEX(C:C,COUNTA(C:C)-2)

Rick Rothstein (MVP - Excel)

Rick, this doesn't return the expected value (as per my understanding
of OP's scenario). Keeping in mind that the expected value is always
'relative' to the first blank row below the newly inserted data. This
means as rows are inserted the previous data is now above the new row.

Possibly, I misunderstand what the OP wants...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc