Created attachment 119032 [details] Tiny Calc Shhet When a source cell with a text string is utilised in a Find or Search function on the contents of a text data cell then a valid hit returns a true value (normally the start position of the source text) which can be subjected to further processing When the source text is not contained in the target string then an #VALUE! result is returned. Clearly this is a terminal failure as no further processing can be performed to provide any state that can subsequently be processed. A small spreadsheet has been appended with a full description of the effect and the one step needed to create the anomaly. Simply type a value into either of the two cells in the first row. A valid "hit" produces a processable return whereas a "miss" provides an illogical or invalid result.
Consulted our Calc dev guru erAck: Those functions are defined to return an error if there is no match; if you don't want to see an error you can use IFERROR()