Return first non blank cell from a given point

[raymaster98]

In Excel 2003, I am using the following formula to return a time:

{=OFFSET(INDEX(BW5:BW24,MATCH(TRUE,BW5:BW24<>"",0)),0,-5)}

I need to modify the above formula to look for the first non blank cell above that point. Any help would be most appreciated. Thanks, Kenny

 

[DonkeyOte]

I'm a tad confused - presumably you're saying the first non-blank above the range returned by the OFFSET ?

I base the above on the fact that the existing function is finding the first non-blank in BW range so by definition there are no non-blanks above that cell (other than perhaps in BW1:BW4)

If so... perhaps you could use:

=LOOKUP(9.99E+307,BR5:INDEX(BR5:BR24,MAX(1,MATCH(TRUE,INDEX(BW5:BW24<>"",0),0)-1)))

The above assume BR5:BR24 contain numerics - based on Time comment