#N/A logic

[garethgtt]

I have the following formula:

=INDEX(Sheet4!$C$1:$C$205,MATCH(1,(E$5=Sheet4!$A$1:$A$2216)*(B7=Sheet4!$B$1:$B$216),0))

now when it does not find a match it displays #N/A -- how can i instead get it to show either nothing or a 0

?
Many thanks

 

[Randombard]

You need to add an "If" statement


=IF(ISERROR(INDEX(Sheet4!$C$1:$C$205,MATCH(1,(E$5=Sheet4!$A$1:$A$2216)*(B7=Sheet4!$B$1:$B$216),0))),0,INDEX(Sheet4!$C$1:$C$205,MATCH(1,(E$5=Sheet4!$A$1:$A$2216)*(B7=Sheet4!$B$1:$B$216),0)))

something like that

 

[banjoflanjo]

Try this:

=if(isna(INDEX(Sheet4!$C$1:$C$205,MATCH(1,(E$5=Sheet4!$A$1:$A$2216)*(B7=Sheet4!$B$1:$B$216),0))),"",INDEX(Sheet4!$C$1:$C$205,MATCH(1,(E$5=Sheet4!$A$1:$A$2216)*(B7=Sheet4!$B$1:$B$216),0)))

for leaving blank.

or,

=if(isna(INDEX(Sheet4!$C$1:$C$205,MATCH(1,(E$5=Sheet4!$A$1:$A$2216)*(B7=Sheet4!$B$1:$B$216),0))),0,INDEX(Sheet4!$C$1:$C$205,MATCH(1,(E$5=Sheet4!$A$1:$A$2216)*(B7=Sheet4!$B$1:$B$216),0)))

for entering a 0.

Hope that helps,

Banjoflanjo

 

[Aladin Akyurek]

I have the following formula:

=INDEX(Sheet4!$C$1:$C$205,MATCH(1,(E$5=Sheet4!$A$1:$A$2216)*(B7=Sheet4!$B$1:$B$216),0))

now when it does not find a match it displays #N/A -- how can i instead get it to show either nothing or a 0

?
Many thanks

If the formula is supposed to return a number when successful...

Control+shift+enter, not just enter:

=LOOKUP(9.99999999999999E+307,
   CHOOSE({1,2},0,INDEX(Sheet4!$C$1:$C$205,MATCH(1,
     IF(E$5=Sheet4!$A$1:$A$2216,IF(B7=Sheet4!$B$1:$B$216,1)),0))))

Note. If you are on Excel 2007 or beyond, have a look at the IFERROR function.

 

[garethgtt]

If the formula is supposed to return a number when successful...

Control+shift+enter, not just enter:

=LOOKUP(9.99999999999999E+307,
   CHOOSE({1,2},0,INDEX(Sheet4!$C$1:$C$205,MATCH(1,
     IF(E$5=Sheet4!$A$1:$A$2216,IF(B7=Sheet4!$B$1:$B$216,1)),0))))

Note. If you are on Excel 2007 or beyond, have a look at the IFERROR function.

Thanks very much!