comparing dates and taking appropriate values if there is if not write -9999

Hi,

I was wondering if someone know how to deal with this topic.

I have to range of dates, one for instance the regular year and the second one, the same year but with some gaps and data going along with.
I am looking for a function that will take each date from the full year and searh for the same date in the range with a gaps and then take its coresponding vvalue if that date exists, if not it should write -9999.


i would really appreciate if someone can help me to resolve this since i have a lot of data like this.


thank you,

See attached file - you can see the formula that I've used in B2:

=IFERROR(INDEX(D:D,MATCH(MROUND(A2,1/24),C:C,0)),-9999)

although it will look different to you with commas changed to semicolons and keyword translated to the French equivalent.

I also used this formula in E2 temporarily:

=MROUND(C2,1/24)

It acts on column C to minimise very small errors which can creep into dates and prevent exact matching. I copied this down, fixed the values, then copied the values into column C to overwrite the values that were there, then deleted the contents of column E.

Hope this helps.

Pete

thanks Pete,

works perfect.

cheers!