Match name and Date Period for %
 

In sheet 1
Name Date %
u 31-Mar-07 3.00%
P 30-Mar-07 1.20%
w 06-May-07 4.50%
w 10-Jun-07 #N/A


In Sheet 2
Name % Start End
p 1.2% 01-Jan-07 31-Mar-07
u 3.0% 09-Feb-07 06-May-07
w 4.5% 06-May-07 08-Jun-07

=INDEX(Sheet2!$B$2:$B$4,MATCH(1,(A1=Sheet2!$A$2:$A $4)*(B1=Sheet2!$C$2:$C$4)*(B1<=Sheet2!$D$2:$D$4), 0))

It is found that if the date specified is out of the range of Sheet 2, we
got the result "#N/A". How could I change it to zero instead of "#N/A" for
my further calculation.

Thanks in advance

Match name and Date Period for %
 

Try:-

IF(ISERROR(INDEX(Sheet2!$B$2:$B$4,MATCH(1,(A2=Shee t2!$A$2:$A$4)*(B2=Sheet2!$C$2:$C$4)*(B2<=Sheet2!$ D$2:$D$4),0))),"0",INDEX(Sheet2!$B$2:$B$4,MATCH(1, (A2=Sheet2!$A$2:$A$4)*(B2=Sheet2!$C$2:$C$4)*(B2<= Sheet2!$D$2:$D$4),0)))

Mike

"aggregate sum over the periods" wrote:

In sheet 1
Name Date %
u 31-Mar-07 3.00%
P 30-Mar-07 1.20%
w 06-May-07 4.50%
w 10-Jun-07 #N/A


In Sheet 2
Name % Start End
p 1.2% 01-Jan-07 31-Mar-07
u 3.0% 09-Feb-07 06-May-07
w 4.5% 06-May-07 08-Jun-07

=INDEX(Sheet2!$B$2:$B$4,MATCH(1,(A1=Sheet2!$A$2:$A $4)*(B1=Sheet2!$C$2:$C$4)*(B1<=Sheet2!$D$2:$D$4), 0))

It is found that if the date specified is out of the range of Sheet 2, we
got the result "#N/A". How could I change it to zero instead of "#N/A" for
my further calculation.

Thanks in advance

Match name and Date Period for %
 

Thanks. I got the result .

"Mike H" wrote:

Try:-

IF(ISERROR(INDEX(Sheet2!$B$2:$B$4,MATCH(1,(A2=Shee t2!$A$2:$A$4)*(B2=Sheet2!$C$2:$C$4)*(B2<=Sheet2!$ D$2:$D$4),0))),"0",INDEX(Sheet2!$B$2:$B$4,MATCH(1, (A2=Sheet2!$A$2:$A$4)*(B2=Sheet2!$C$2:$C$4)*(B2<= Sheet2!$D$2:$D$4),0)))

Mike

"aggregate sum over the periods" wrote:

In sheet 1
Name Date %
u 31-Mar-07 3.00%
P 30-Mar-07 1.20%
w 06-May-07 4.50%
w 10-Jun-07 #N/A


In Sheet 2
Name % Start End
p 1.2% 01-Jan-07 31-Mar-07
u 3.0% 09-Feb-07 06-May-07
w 4.5% 06-May-07 08-Jun-07

=INDEX(Sheet2!$B$2:$B$4,MATCH(1,(A1=Sheet2!$A$2:$A $4)*(B1=Sheet2!$C$2:$C$4)*(B1<=Sheet2!$D$2:$D$4), 0))

It is found that if the date specified is out of the range of Sheet 2, we
got the result "#N/A". How could I change it to zero instead of "#N/A" for
my further calculation.

Thanks in advance