Lookup a value, and SUM relative to that value position

[gtichauer]

Hi,

Based on a date on actual worksheet I need to find that date in another worksheet, and make a SUM a the range relative to the cell matched. This is if value matched is on cell A1 I need to SUM C1:C7 (this part I know how to do with offset but I can't get the first part).

Attached is an example for more clear view.

Thanks,