If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using INDEX and it Returns a #N/A
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. |
#2
|
|||
|
|||
Using INDEX and it Returns a #N/A
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. |
#3
|
|||
|
|||
Using INDEX and it Returns a #N/A
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 - |
Thread Tools | |
Display Modes | |
|
|