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

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

 

[Bernard Liengme]

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

 

[Jim Thomlinson]

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

 

[Wildwood]

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

 

[Wildwood]

Thanks - I tried this but it doesnt work in my sheet!

 

[Wildwood]

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

 

[Wildwood]

Eureka! It works! Thanks!!!!!!

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

 

[Jim Thomlinson]

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.