If the data contains numerical values...
=LOOKUP(9.99999999999999E+307,A2:INDEX(A2:A100,MATCH(9.99999999999999E+30
7,A2:A100)-1))
If the data contains text values...
=LOOKUP(REPT("z",255),A2:INDEX(A2:A100,MATCH(REPT("z",255),A2:A100)-1))
Adjust the ranges accordingly.
Hope this helps!