Excel's Find function; Exceptions over values

Question

I recently learned that Excel's Find function returns a #VALUE error when it doesn't find the needle in the haystack (i.e. no match is found). I have several questions about this behavior:

1. Is there another Excel function that works as Find but returns an actual value (e.g. -1) when no match is found?
2. Is there any well-known reason for the function having that behavior? I mean, talking about general programming and software design, Is there a known pattern (or methodology, or design philosophy) that prefers throwing exceptions over returning values (like -1, 0, "" or similar) when a function doesn't return a "valid" value?

Answer 1

The Find function looks for a case-sensitive match, and can be tested with IsNumber, like:

=IF(ISNUMBER(FIND("abc",A1)),FIND("abc",A1),"No exact match")

There is a very similar function called Search which does the same thing, but is not case sensitive. It also returns an error if no match is found.

So if you are looking for something and want to return -1 if there is no match and you are not worried about being case sensitive, then something like this should work for you:

=IF(COUNTIF(A1,"*abc*")=0,-1,SEARCH("abc",A1))

For case sensitive searches, it would be:

=IF(ISNUMBER(FIND("abc",A1)),FIND("abc",A1),-1)

If you are on Excel 2007 or later, you can skip the error checking by using IFERROR:

=IFERROR(SEARCH("abc",A1),-1)
=IFERROR(FIND("abc",A1),-1)