If, VLookUp, and date. Future date Lookup problem.

[mike1984]

Goal: if today is greater than last day of month, if true then last result of month, if false: lookup yesterday in range $E$62:$AK$645, column 2.

This is the formula:

=IF(TODAY()>$E$643,F$643,VLOOKUP($BK$1,$E$62:$AK$645,2))

Col E= all dates of the year. $E$643 is the 31st December.
Col F= daily accounting results. F$643 being next to 31st December.
$BK$1= TODAY()-1

Problem: December is showing the results of november. What can i do to show no results if that is in the future?

Thanks for the help.

 

[mike1984]

Is my question too complicated? too simple? not enough information? I would really appreciate some help.

 

[Aladin Akyurek]

Care to elaborate on the problem you signal? That is, what do you mean by "december is showing the results of november"? I'd think if a december date (BK1) is not available in $E$62:$E$645, that is, BK1 > MAX($E$62:$E$645), then you'll certainly get what is associated with the Max value.

 

[mike1984]

 

[mike1984]

as you can see in the image i posted. Every month is seperate, yet the whole year is on one sheet. Also notice the balance row, thats where my formula is.

my lookup range is the whole year, column 1 being "E" where my dates are. Col 2 is "F" and so on. im asuming my problem lies in my Vlookup.

i get the results from november in the balance row (view image). i want this balance row from december to show "0" or "-". what would be the best way to go about this? any ideas?