Many thanks Miguel,

The formula still seems to have to exactly match the day part of $K2 to pull
through $F2 however. I need to match the year and month parts of $K2 only.

Cheers

RGB


"Miguel Zapico" wrote:

You may use the AND function there, as you did before
=IF(AND(L$1=data!$K2, L$1<=data!$H2),
IF(AND(MONTH(L$1)=MONTH(data!$K2),YEAR(L$1)=YEAR(d ata!$K2)),$F2,data!$I2/data!$G2),"")

Hope this helps,
Miguel.

"RGB" wrote:

hello,

I have the following formula which works fine;

=IF(AND(L$1=data!$K2, L$1<=data!$H2),
(IF(MONTH(L$1)=MONTH(data!$K2),$F2,data!$I2/data!$G2)),"")

I need to change it so that the bit (IF(MONTH(L$1)=MONTH(data!$K2) checks
that a date matches not only the month but the year also (i dont need to
match the day).

I tried this; - but excel says that it contains an error!

=IF(AND(L$1=data!$K2, L$1<=data!$H2), (IF(MONTH(L$1),(YEAR
(L$1))=(MONTH(data!$K2)),(YEAR (L$1)),$F2,data!$I2/data!$G2)),"")

Any ideas how I can correct (IF(MONTH(L$1),(YEAR
(L$1))=(MONTH(data!$K2)),(YEAR (L$1)) to work?

many thanks

rgb