Match name and Date Period for %

  • Thread starter aggregate sum over the periods
  • Start date
A

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
 
M

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
 
A

aggregate sum over the periods

Thanks. I got the result .

Mike H said:
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
 

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