Match name and Date Period for %

[aggregate sum over the periods]

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

 

[Mike H]

Try:-

IF(ISERROR(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))),"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]

Thanks. I got the result .

Try:-

IF(ISERROR(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))),"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