Index/Match formula #N/A error
Posted by Anthony on February 04, 2002 10:53 AM
Hello to all!!! When I apply the following formula:
=INDEX(Format_Update!C$2:D$423, MATCH(L42,Format_Update!C$2:C$423,0), 2)
the error #N/A appears if the data I'm tryng to match is not in the index. I would like a "0" to appear instead. Is this possible?
Thanks
Anthony
Posted by Bagsy Baker on February 04, 2002 11:41 AM
Add the if(ISERROR( statement to the start of your formula and make the TRUE part equal 0 as shown
Good luck
P.S. My brackets may be wrong as I did this off the cuff.
Posted by Scott on February 04, 2002 11:58 AM
Use IF(ISNA instead
Although "IF(ISERROR will replace the "#N/A" with "0", it will also replace any other errors which could lead to problems. By using "IF(ISNA" you would be addressing only the problem of the data not being found in your table, and not a possible forumla error.
Posted by Aladin Akyurek on February 04, 2002 12:32 PM
Anthony --
Since you're doing an exact match, the following is a reasonably efficient way of returning zero when MATCH fails:
=IF(COUNTIF(Format_Update!C$2:C$423,L42), INDEX(Format_Update!C$2:D$423, MATCH(L42,Format_Update!C$2:C$423, 0), 2), 0)
Aladin
===========
Posted by Anthony on February 04, 2002 12:58 PM
Re: Use IF(ISNA instead
I tried both ways it's stating that there is an error with the formula.....it highlights the 0. I tried putting it in quotes but it doesn't seem to help. Any other suggestions?
Thank you both for responding!
Anthony
Posted by Anthony on February 04, 2002 1:07 PM
Re: Use IF(ISNA instead
It worked....thanks Aladin!!! I'm still curious as to why I couldn't get the others to work. Perhaps someone can drop me a quick explanation. Thanks again!
Anthony
Posted by Scott on February 04, 2002 1:18 PM
Re: Use IF(ISNA instead
Not sure why it's not working. Using your original formula, it should read like this:
=IF(ISNA(INDEX(Format_Update!C$2:D$423, MATCH(L42,Format_Update!C$2:C$423,0),2)), 0 ,INDEX(Format_Update!C$2:D$423, MATCH(L42, Format_Update!C$2:C$423,0),2))
