Selecting value from second list when date is within date range on first list

[Steven Reid]

Hi All,

I have a worksheet where I have stock prices listed with the From Date and a
To Date.

I need to be able to add an additional dividend amount to table one when a
dividend was paid in that period.

Can anyone help in the formula

This is the data to begin with

Row A B C D E
F G
Col
Table One
Table Two

Date_From Date_to Dividend Price Date
Dividend
1 1/07/2004 31/07/2004 $10.50 13/06/2004
$1.43
2 1/06/2004 30/06/2004 $9.53 18/04/2004
$1.24
3 1/05/2004 31/05/2004 $10.10 12/03/2004
$1.32
4 1/04/2004 30/04/2004 $9.20 19/09/2003
$1.22
5 1/03/2004 31/03/2004 $9.28
6 1/02/2004 29/02/2004 $9.12
7 1/01/2004 31/01/2004 $8.95
8 1/12/2003 31/12/2003 $8.79
9 1/11/2003 30/11/2003 $8.62
10 1/10/2003 31/10/2003 $8.46
11 1/09/2003 30/09/2003 $8.29
12 1/08/2003 31/08/2003 $8.13


And i want it to end up like this.


Row A B C D E
F G
Col
Table One
Table Two

Date_From Date_to Dividend Price Date
Dividend
1 1/07/2004 31/07/2004 $10.50 13/06/2004
$1.43
2 1/06/2004 30/06/2004 $1.43 $9.53 18/04/2004
$1.24
3 1/05/2004 31/05/2004 $10.10 12/03/2004
$1.32
4 1/04/2004 30/04/2004 $1.24 $9.20 19/09/2003
$1.22
5 1/03/2004 31/03/2004 $1.32 $9.28
6 1/02/2004 29/02/2004 $9.12
7 1/01/2004 31/01/2004 $8.95
8 1/12/2003 31/12/2003 $8.79
9 1/11/2003 30/11/2003 $8.62
10 1/10/2003 31/10/2003 $8.46
11 1/09/2003 30/09/2003 $8.29
12 1/08/2003 31/08/2003 $1.22 $8.13


Thanks in advance

Steve Reid

 

[Steven Reid]

Sorry, Hope this lines up better

This is the data to begin with

Table One Table Two
Date_From Date_to Dividend Price Date
Dividend
1/07/2004 31/07/2004 $10.50 13/06/2004 $1.43
1/06/2004 30/06/2004 $9.53 18/04/2004 $1.24
1/05/2004 31/05/2004 $10.10 12/03/2004 $1.32
1/04/2004 30/04/2004 $9.20 19/09/2003 $1.22
1/03/2004 31/03/2004 $9.28
1/02/2004 29/02/2004 $9.12
1/01/2004 31/01/2004 $8.95
1/12/2003 31/12/2003 $8.79
1/11/2003 30/11/2003 $8.62
1/10/2003 31/10/2003 $8.46
1/09/2003 30/09/2003 $8.29
1/08/2003 31/08/2003 $8.13

And i want it to end up like this.

Table One Table Two
Date_From Date_to Dividend Price Date
Dividend
1/07/2004 31/07/2004 $10.50 13/06/2004 $1.43
1/06/2004 30/06/2004 $1.43 $9.53 18/04/2004 $1.24
1/05/2004 31/05/2004 $10.10 12/03/2004 $1.32
1/04/2004 30/04/2004 $1.24 $9.20 19/09/2003 $1.22
1/03/2004 31/03/2004 $1.32 $9.28
1/02/2004 29/02/2004 $9.12
1/01/2004 31/01/2004 $8.95
1/12/2003 31/12/2003 $8.79
1/11/2003 30/11/2003 $8.62
1/10/2003 31/10/2003 $8.46
1/09/2003 30/09/2003 $1.22 $8.29
1/08/2003 31/08/2003 $8.13

 

[Domenic]

Hi Steven,

Try,

=IF(ISNA(INDEX($F$2:$F$5,MATCH(1,(MONTH($E$2:$E$5)=MONTH(B2))*(YEAR($E$2:
$E$5)=YEAR(B2)),0))),"",INDEX($F$2:$F$5,MATCH(1,(MONTH($E$2:$E$5)=MONTH(B
2))*(YEAR($E$2:$E$5)=YEAR(B2)),0)))

Hope this helps!

 

[Domenic]

Sorry! Forgot to mention, enter this formula using CTRL+SHiFT+ENTER.

 

[Steven Reid]

Thanks Domenic,

I will have a look at it.

Steve

 

[Steven Reid]

Hi Domenic,

Your solution worked a treat but I also found the following solution
which is a little bit easier to read and a whole lot easier to write



{=SUMPRODUCT(--($I$3:$I$66>=B3),--($I$3:$I$66<=C3),$J$3:$J$66)}

Where 'B3' is Date_From and 'C3' is Date_To,
the 'I' range is the Date column of the dividend and the 'J' column is
the anount of the dividend.

This formula will also allow for the summing of any values where
multiple dividends were paid in that period. e.g. if two dividends
were paid in the month of July then the total amount will be included
in the dividen column.



This is input as an array - so you need to use Ctrl-Shift-Enter
The -- in the formula returns a 1 or 0 instead of True and False


Thanks again

Steve