how to substitute or mask “#VALUE!” on the fly without creating a intermediate cell/formula
I have the following formula:
=IF(SEARCH(“XYZ”, Product),Product, 0)
where:
Product is a named area in a spreadsheet.
From time to time the pattern “XYZ” is not found (and it should be so) and the formula returns #VALUE! , as it should.
In a different blog/thread I have found that I can use
=IF(COUNTIF(A1,”#Value!”),NA(),””)
=IF(ISERROR(A1),NA(),””)
to mask the a specific error, or value, or to capture all error cases.
But joining my formula and one of the previous suggestions, I receive an error and the formula is not accepted by excel.
=IF( COUNTIF( SEARCH(“XYZ”, Product) , “#Value!” ), Product, 0)
(I have added “blanks” to improve readability).
Using:
=IF( ISERROR( SEARCH(“XYZ”, Product) ) , Product, 0)
does nothing reasonable and always shows the value of Product as if no error is ever raised, indeed it is as if the SEARCH function was not there.
Any suggestions?
thx
I have the following formula:=IF(SEARCH(“XYZ”, Product),Product, 0)where:Product is a named area in a spreadsheet.From time to time the pattern “XYZ” is not found (and it should be so) and the formula returns #VALUE! , as it should. In a different blog/thread I have found that I can use=IF(COUNTIF(A1,”#Value!”),NA(),””)=IF(ISERROR(A1),NA(),””)to mask the a specific error, or value, or to capture all error cases. But joining my formula and one of the previous suggestions, I receive an error and the formula is not accepted by excel.=IF( COUNTIF( SEARCH(“XYZ”, Product) , “#Value!” ), Product, 0)(I have added “blanks” to improve readability). Using:=IF( ISERROR( SEARCH(“XYZ”, Product) ) , Product, 0)does nothing reasonable and always shows the value of Product as if no error is ever raised, indeed it is as if the SEARCH function was not there. Any suggestions?thx Read More
