find and replace text from result not from the formula

[ashish514]

I have a column which contains a certain formula. In some of the cells in the same column there is the error message #N/A. I need to replace such cells with blank cells. But when i am using the find and replace feature, it looks into the formula in the cells and not the results and thus is unable to replace #N/A. Is there some method by which i can do that?? Please help..

 

[JackDanIce]

If your formula is something like:

=1/0

Which returns "#DIV/0"

You can change the formula to:

=IF(ISERROR(1/0),"","No error")

This will return what appears to be a blank cell because 1/0 results in an error

=IF(ISERROR(1/1),"","No error")

Will return "No error"

Play with it and adapt to your formula, unless you care to provide your formula

 

[ashish514]

Well, this is my formula
{=INDEX(Fam!$D$2:$D$1974,MATCH(1,(Fam!$A$2:$A$1974=A1)*(Fam!$B$2:$B$1974=B1),0))}

And the error is #N/A and not #DIV/0

And I need to use the resulting column as a reference in another SUMPRODUCT formula. But sumproduct will give the same error unless the whole column is error free. Only if the cells having errors could be cleared and left blank, it would work.

 

[cameron.richens]

TRY THIS


=IF(ISERROR(INDEX(Fam!$D$2:$D$1974,MATCH(1,(Fam!$A$2:$A$1974=A1)*(Fam!$B$2:$B$1974=B1),0))),"",INDEX(Fam!$D$2:$D$1974,MATCH(1,(Fam!$A$2:$A$1974=A1)*(Fam!$B$2:$B$1974=B1),0)))

ISNA also works... but only for N/A errors