match the date and paste certain value if not write -9999

[sensation]

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.

so my date are as follows:

regular year	result should be like this here	year with gaps	values of years with gaps
01/01/2011 00:00 <tbody> </tbody>	0.5	01/01/2011 00:00	0.5
01/01/2011 01:00 <tbody> </tbody>	-9999	01/01/2011 02:00	0.7
01/01/2011 02:00 <tbody> </tbody>	0.7	01/01/2011 03:00	0.4
01/01/2011 03:00 <tbody> </tbody>	0.4	01/01/2011 04:00	0.3
01/01/2011 04:00 <tbody> </tbody>	0.4	etc
etc

<tbody>
</tbody>

i have tried to use look up but didnt manage, if someone can help me would appreciate it.

regards,

marc

 

[AlphaFrog]

A B C D 1 regular year result year with gaps values 2 1/1/2011 0:00​ 0.5​ 1/1/2011 0:00​ 0.5​ 3 1/1/2011 1:00​ -9999​ 1/1/2011 2:00​ 0.7​ 4 1/1/2011 2:00​ 0.7​ 1/1/2011 3:00​ 0.4​ 5 1/1/2011 3:00​ 0.4​ 1/1/2011 4:00​ 0.3​ 6 1/1/2011 4:00​ 0.3​

Worksheet Formulas

Cell Formula B2 =IFERROR(VLOOKUP(A2,C:D,2,0),-9999)

 

[sensation]

A B C D 1 regular year result year with gaps values 2 1/1/2011 0:00 ​ 0.5​ 1/1/2011 0:00​ 0.5​ 3 1/1/2011 1:00 ​ -9999​ 1/1/2011 2:00 ​ 0.7​ 4 1/1/2011 2:00​ 0.7​ 1/1/2011 3:00​ 0.4​ 5 1/1/2011 3:00​ 0.4​ 1/1/2011 4:00​ 0.3​ 6 1/1/2011 4:00​ 0.3​ <tbody> </tbody>

<tbody>
</tbody>

Worksheet Formulas

Cell Formula B2 =IFERROR(VLOOKUP(A2,C:D,2,0),-9999) <tbody> </tbody>

<tbody>
</tbody>

hi, i try to usi that formula but when i run with these data i dont get matching values eg.: i am using = IFERROR(VLOOKUP(A2,C$2:D$20,2,0),-9999)

A B C D

01/01/2011 00:00	0	01/01/2011 00:00	0
01/01/2011 01:00	0	01/01/2011 01:00	0
01/01/2011 02:00	-9999	01/01/2011 02:00	0
01/01/2011 03:00	0	01/01/2011 03:00	0
01/01/2011 04:00	0	01/01/2011 04:00	0
01/01/2011 05:00	-9999	01/01/2011 05:00	0
01/01/2011 06:00	0	01/01/2011 06:00	0
01/01/2011 07:00	0	01/01/2011 07:00	0
01/01/2011 08:00	-9999	01/01/2011 08:00	0
01/01/2011 09:00	0	01/01/2011 09:00	0
01/01/2011 10:00	0	01/01/2011 10:00	0
01/01/2011 11:00	-9999	01/01/2011 11:00	0
01/01/2011 12:00	0	01/01/2011 12:00	0
01/01/2011 13:00	0	01/01/2011 13:00	0
01/01/2011 14:00	-9999	01/01/2011 14:00	0
01/01/2011 15:00	0	01/01/2011 15:00	0
01/01/2011 16:00	0	01/01/2011 16:00	0
01/01/2011 17:00	-9999	01/01/2011 18:00	0
01/01/2011 18:00	0	01/01/2011 21:00	0
01/01/2011 19:00	-9999
01/01/2011 20:00	-9999
01/01/2011 21:00	0

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

thanks

 

[AlphaFrog]

What is the value in cell A2 as seen in the formula bar?
What is the value in column C as seen in the formula bar that is suppose to match A2?

You can easily test if two values truly match with something like
=A2=C10
This will return TRUE or FALSE if they match or not.