how to substitute or mask "#VALUE!" on the fly without creating a intermediate cell/formula

Copper Contributor

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

 

3 Replies

@pippo02 

Not sure what exactly you are trying to achieve, but perhaps this one is what you need:

=IFERROR(SEARCH("XYZ", product), 0)
Thx
I used
=IF(IFERROR(SEARCH("XYZ", Product), 0),Product,0)
and now it works (again I wanted the 'answer' w/o an in-between calculation.
Your formula gives 0 or 1 and not the name of the product or 0
Thanks again

@pippo02 

You may try

=IF(SUM(--ISNUMBER(SEARCH("XYZ", product))),product, 0)