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
=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))))
If the formula is supposed to return a number when successful...
Control+shift+enter, not just enter:
Code:=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.