SUMPRODUCT OR LOOKUP

[Frick]

Hi:

On sheet1 in cells C1:N1 are dates starting on the first of the year
(Example C1=1/1/04)
On sheet1 in cells C2:N2 are dates for the end of the year (Example
C2=>EOMONTH(C1,11)=>12/31/04)
On sheet1 in cells C4:N4 are data (Example C4=5, D4=27, E4=12 ETC.)


On sheet2 in cells B10:AZ10 are dates starting with the first of the month
(Example B10=1/1/05)
On sheet2 in cells B11:AZ11 are dates for the end of the month (Example
B11=EOMONTH(B10,0)

On sheet2 in cells B14:AZ14 I need a formula that will:

Using the date range in the col (Example sheet2 B10 and B11) find the
reference date range in sheet1 and return the data.

For example:

if sheet2 B10=1/1/05 and B11=1/31/05 and
in sheet1 if D1=1/1/05 and D2=12/31/05 then
any dates in sheet2 in cells B14:az14 where
cells B10=1/1/05 and B11=1/31/05 then
the formula return would be 27

Thanks

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX('sheet1'$C$4:$N$4,MATCH(YEAR($B$10),YEAR('sheet1'!$C$2:$N$2),0))