Return first non blank in the following array if cell is blank

[stepan1987]

Hi guys,

I need a formula that would return me the value from the cell that is found by vlookup (equal dates). In case there is no value in that cell I need the formula to return me the first non blank value in the cells above that cell.

Here is the formula I use now:
=VLOOKUP($H$2;'Historical Data'!$A:$EZ;MATCH($AQ17;'Historical Data'!$B$1:$EZ$1;0)+1)

$H$2 - date needed which is vlookuped in Historical Data sheet
$AQ17 - name I need value for

Bottomline: I need the first nonblank value on that date or before it

How could that be done? Pls help

 

[stepan1987]

Maybe any ideas guys?
I need smth like

IF(A1=nonblank; A1; (IF(A2=nonblank; A2; IF(A3=nonblank; A3; IF(A4=... until first non blank.. there must be smth similar

 

[stepan1987]

Maybe anyone has some suggestions? I have no clue how to make it at best.