Expert: Miguel Zapico - 10/21/2009

Question
I have a set of 10 strings in 10 different cells. I want to test to see if a certain text string is in any of these 10 cells. My approach is to use the CONCATENATE fx to combine all the the cells and then use the FIND or SEARCH fx to check and see if the string I'm looking for is present in the combined string. I then want a cell to display a certain value if the text is there, and another value if it is not there. It works fine if the text value is present. I just use an IF statement to see if the SEARCH fx returns a value. The problem is that if the text I'm looking for is not there the SEARCH returns a #VALUE. Is there anyway to use the #VALUE return in a logical test, or is there another fx that will perform the task I need?

Answer
You can use the ISERROR formula for this.  For example:
=IF(ISERROR(Your_condition_here),"Error",Your_result)
The ISERROR formula returns TRUE if its parameters evaluates to an error, and FALSE otherwise.  You can use it on your formulas as you see fit.

Hope this helps,
Miguel