How to convert #N/A to 0 using Match?

  • Thread starter Thread starter Wildwood
  • Start date Start date
W

Wildwood

Hi - -

Here is my formula. What changes do I need to make to return a "0" instead
of "#N/A"?

=INDEX('Oct 09 Discrepancy'!$A$1:$C$875, MATCH("0120 Count",'Oct 09
Discrepancy'!$A$1:$A$875,), MATCH("Counts",'Oct 09 Discrepancy'!$A$1:$C$1,))
 
Try testing your formula with ISNA()
=IF(ISNA(your_formula), 0, your_formula)

If you are using Excel 2007 try
=IFERROR(your_formula,0)

best wishes
 
Check out: =IF(ISNA(....
Micky
*** In "2007" it might be sinpler and shorter ***
Micky
 
I would be inclined to use this...

=if(or(countif('Oct 09 Discrepancy'!$A$1:$A$875, "0120 Count") = 0,
countif('Oct 09 Discrepancy'!$A$1:$C$1, "Counts") = 0), 0, INDEX('Oct 09
Discrepancy'!$A$1:$C$875, MATCH("0120 Count",'Oct 09
Discrepancy'!$A$1:$A$875,), MATCH("Counts",'Oct 09 Discrepancy'!$A$1:$C$1,)))

Since you are obvioulsy looking for text the Countif has no distinct
advantage over ISNA but there is no great down side either. If you were
looking for numbers then countif is better than ISNA (IMO) is it will return
NA if the data type is mismatched (looking up text in numbers or vice versa).
 
I tried this with no luck??? What am I missing?

=IFERROR(INDEX('Oct 09 Discrepancy'!$A$1:$C$875, MATCH("0166 Count",'Oct 09
Discrepancy'!$A$1:$A$875,), MATCH("Counts",'Oct 09
Discrepancy'!$A$1:$C$1,))),0, INDEX('Oct 09 Discrepancy'!$A$1:$C$875,
Match("0166 Count",'Oct 09 Discrepancy'!$A$1:$A$875,) Match("Counts",'Oct 09
Discrepancy'!$A$1:$C$1,)))
 
Here is what I have - - yet it isnt working.....Any ideas???

=IF(ISNA(INDEX('1st Month Discrepancy'!$A$1:$C$875, MATCH("0106 Count",'1st
Month Discrepancy'!$A$1:$A$875,), MATCH("Counts",'1st Month
Discrepancy'!$A$1:$C$1,)),0, INDEX('1st Month Discrepancy'!$A$1:$C$875,
MATCH("0106 Count",'1st Month Discrepancy'!$A$1:$A$875,), MATCH("Counts",'1st
Month Discrepancy'!$A$1:$C$1,))
 
Eureka! It works! Thanks!!!!!!

Wildwood said:
I tried this with no luck??? What am I missing?

=IFERROR(INDEX('Oct 09 Discrepancy'!$A$1:$C$875, MATCH("0166 Count",'Oct 09
Discrepancy'!$A$1:$A$875,), MATCH("Counts",'Oct 09
Discrepancy'!$A$1:$C$1,))),0, INDEX('Oct 09 Discrepancy'!$A$1:$C$875,
Match("0166 Count",'Oct 09 Discrepancy'!$A$1:$A$875,) Match("Counts",'Oct 09
Discrepancy'!$A$1:$C$1,)))
 
Define... Doesn't work. is it returning nothing? Is it returning an error? is
it returning the wrong value? We do not have your source data so we can not
test your formula to see any problems for ourselves so you need to be
explicit in your descriptions.
 
Back
Top