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)


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



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



=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?



3 Replies


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

=IFERROR(SEARCH("XYZ", product), 0)
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


You may try

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