I am using the Function INDEX and MATCH. It works. It does not work well when
you want to add up the results but the result returns a #N/A. I would like to
return a Zero instead on a #N/A. Here is my Formula that I am using.

=INDEX(Detail!$F$2:$F$1292,MATCH(1,(Detail!$A$2:$A $1292=Summary!C$5)*(Detail!$G$2:$G$1292=Summary!$A $11),0))

This returns #N/A and I would like it to return a Zero.

Thanks in Advance for any help.

Here is the answer.


=IF(ISNA(INDEX(Detail!$F$2:$F$1292,MATCH(1,(Detail !$A$2:$A$1292=Summary!R$5)*(Detail!$G$2:$G$1292=Su mmary!$A$10),0))),0,INDEX(Detail!$F$2:$F$1292,MATC H(1,(Detail!$A$2:$A$1292=Summary!R$5)*(Detail!$G$2 :$G$1292=Summary!$A$10),0)))

It works every time.

"Crazyhorse" wrote:

I am using the Function INDEX and MATCH. It works. It does not work well when
you want to add up the results but the result returns a #N/A. I would like to
return a Zero instead on a #N/A. Here is my Formula that I am using.

=INDEX(Detail!$F$2:$F$1292,MATCH(1,(Detail!$A$2:$A $1292=Summary!C$5)*(Detail!$G$2:$G$1292=Summary!$A $11),0))

This returns #N/A and I would like it to return a Zero.

Thanks in Advance for any help.

Actually, it's the MATCH part that returns #NA if there is no match,
so you could improve it slightly by having:

=IF(ISNA(MATCH(...)),0,INDEX(...MATCH(...)...))

Hope this helps.

Pete

On Jan 8, 4:52*pm, Crazyhorse
wrote:
Here is the answer.

=IF(ISNA(INDEX(Detail!$F$2:$F$1292,MATCH(1,(Detail !$A$2:$A$1292=Summary!R$5*)*(Detail!$G$2:$G$1292=S ummary!$A$10),0))),0,INDEX(Detail!$F$2:$F$1292,MAT C*H(1,(Detail!$A$2:$A$1292=Summary!R$5)*(Detail!$G $2:$G$1292=Summary!$A$10),0*)))

It works every time.



"Crazyhorse" wrote:
I am using the Function INDEX and MATCH. It works. It does not work well when
you want to add up the results but the result returns a #N/A. I would like to
return a Zero instead on a #N/A. Here is my Formula that I am using.

=INDEX(Detail!$F$2:$F$1292,MATCH(1,(Detail!$A$2:$A $1292=Summary!C$5)*(Detai*l!$G$2:$G$1292=Summary!$ A$11),0))

This returns #N/A and I would like it to return a Zero.

Thanks in Advance for any help.- Hide quoted text -

- Show quoted text -