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

S

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
 
S

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
 
D

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!
 
S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top