MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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))