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

S

sensation

New Member
Joined
Dec 5, 2013
Messages
2
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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]regular year[/TD]
[TD]result should be like this here[/TD]
[TD]year with gaps [/TD]
[TD]values of years with gaps[/TD]
[/TR]
[TR]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154, align: right"]01/01/2011 00:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0.5[/TD]
[TD]01/01/2011 00:00
[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154, align: right"]01/01/2011 01:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]-9999[/TD]
[TD]01/01/2011 02:00[/TD]
[TD]0.7[/TD]
[/TR]
[TR]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154, align: right"]01/01/2011 02:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0.7[/TD]
[TD]01/01/2011 03:00[/TD]
[TD]0.4[/TD]
[/TR]
[TR]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154, align: right"]01/01/2011 03:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0.4[/TD]
[TD]01/01/2011 04:00[/TD]
[TD]0.3[/TD]
[/TR]
[TR]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154, align: right"]01/01/2011 04:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0.4[/TD]
[TD]etc[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
i have tried to use look up but didnt manage, if someone can help me would appreciate it.

regards,

marc
 
Sort by date Sort by votes
[table="width: 616, class: grid, align: left"][tr][td]
[table="width: 616, class: grid, align: left"]
[tr][th][/th][th]A[/th][th]B[/th][th]C[/th][th]D[/th][/tr]
[tr][th]1[/th][td]regular year[/td][td]result[/td][td]year with gaps[/td][td]values[/td][/tr]
[tr][th]2[/th][td]
1/1/2011 0:00​
[/td][td]
0.5​
[/td][td]
1/1/2011 0:00​
[/td][td]
0.5​
[/td][/tr]
[tr][th]3[/th][td]
1/1/2011 1:00​
[/td][td]
-9999​
[/td][td]
1/1/2011 2:00​
[/td][td]
0.7​
[/td][/tr]
[tr][th]4[/th][td]
1/1/2011 2:00​
[/td][td]
0.7​
[/td][td]
1/1/2011 3:00​
[/td][td]
0.4​
[/td][/tr]
[tr][th]5[/th][td]
1/1/2011 3:00​
[/td][td]
0.4​
[/td][td]
1/1/2011 4:00​
[/td][td]
0.3​
[/td][/tr]
[tr][th]6[/th][td]
1/1/2011 4:00​
[/td][td]
0.3​
[/td][td][/td][td][/td][/tr]
[/table]
[/td][/tr][/table]

[table="width: 0, class: grid, align: left"][tr][td]Worksheet Formulas[/td][/tr][tr][td][table="width: 0, class: grid, align: left"]
[tr][th]Cell[/th][th]Formula[/th][/tr][tr][th]B2[/th][td]=IFERROR(VLOOKUP(A2,C:D,2,0),-9999)[/td][/tr]
[/table]
[/td][/tr][/table]
 
Upvote 0
AlphaFrog said:
[TABLE="class: grid, width: 616, align: left"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 616, align: left"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[/TR]
[TR]
[TH]1[/TH]
[TD]regular year[/TD]
[TD]result[/TD]
[TD]year with gaps[/TD]
[TD]values[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]
1/1/2011 0:00
​
[/TD]
[TD]
0.5​
[/TD]
[TD]
1/1/2011 0:00​
[/TD]
[TD]
0.5​
[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]
1/1/2011 1:00
​
[/TD]
[TD]
-9999​
[/TD]
[TD]
1/1/2011 2:00
​
[/TD]
[TD]
0.7​
[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]
1/1/2011 2:00​
[/TD]
[TD]
0.7​
[/TD]
[TD]
1/1/2011 3:00​
[/TD]
[TD]
0.4​
[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]
1/1/2011 3:00​
[/TD]
[TD]
0.4​
[/TD]
[TD]
1/1/2011 4:00​
[/TD]
[TD]
0.3​
[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD]
1/1/2011 4:00​
[/TD]
[TD]
0.3​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 0, align: left"]
<tbody>[TR]
[TD]Worksheet Formulas[/TD]
[/TR]
[TR]
[TD][TABLE="class: grid, width: 0, align: left"]
<tbody>[TR]
[TH]Cell[/TH]
[TH]Formula[/TH]
[/TR]
[TR]
[TH]B2[/TH]
[TD]=IFERROR(VLOOKUP(A2,C:D,2,0),-9999)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...






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
[TABLE="width: 448"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]01/01/2011 00:00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]01/01/2011 00:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2011 01:00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]01/01/2011 01:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2011 02:00[/TD]
[TD="align: right"]-9999[/TD]
[TD="align: right"]01/01/2011 02:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2011 03:00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]01/01/2011 03:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2011 04:00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]01/01/2011 04:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2011 05:00[/TD]
[TD="align: right"]-9999[/TD]
[TD="align: right"]01/01/2011 05:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2011 06:00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]01/01/2011 06:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2011 07:00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]01/01/2011 07:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2011 08:00[/TD]
[TD="align: right"]-9999[/TD]
[TD="align: right"]01/01/2011 08:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2011 09:00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]01/01/2011 09:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2011 10:00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]01/01/2011 10:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2011 11:00[/TD]
[TD="align: right"]-9999[/TD]
[TD="align: right"]01/01/2011 11:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2011 12:00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]01/01/2011 12:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2011 13:00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]01/01/2011 13:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2011 14:00[/TD]
[TD="align: right"]-9999[/TD]
[TD="align: right"]01/01/2011 14:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2011 15:00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]01/01/2011 15:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2011 16:00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]01/01/2011 16:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2011 17:00[/TD]
[TD="align: right"]-9999[/TD]
[TD="align: right"]01/01/2011 18:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2011 18:00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]01/01/2011 21:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2011 19:00[/TD]
[TD="align: right"]-9999[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2011 20:00[/TD]
[TD="align: right"]-9999[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2011 21:00[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



thanks
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
580
XLfriend777
XLfriend777
I
  • Question Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
1K
Insurance Dave
I
P
  • Question Question
Offsetting dates / usage in vlookup or index/match function
Replies
1
Views
599
GlennUK
G
A
  • Solved
Macro getting stuck on average when no data is present
Replies
5
Views
478
amphead
A
J
  • Solved
Sum of year to date
Replies
3
Views
680
ExceLoki
ExceLoki

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top