Is there any reason you can't just sort the sheet descending by column A?

If you can't, this would be easiest in VB.


However if you need to do this with formulas, for column D you could potentially use MAX combined with other formulas. D1 is an easy win (MAX). D2 you would need to find the cell reference in column A that matches D1, then get a MAX for above and below that cell. Same concept for D3, just lots more matching and maxing.

Then in column E use something like this:

=INDEX(B:B,MATCH(D1,A:A))


Hope that helps.