19 February 2007

Excel Nested Conditional Find String

I wanted an Excel nested conditional worksheet statement to produce different output in a cell depending on whether another cell in the row had particular sub-string. For instance, if the source cell had "Problem in XYZ" or "Issue found in product ABC", then the dependent cell would show "XYZ" or "ABC". Then I can filter the rows in the worksheet based on the name of the product. Below is how I thought I could write the statement (formatted for readability):

=IF(FIND("XYZ",C1)>=0, "XYZ",
    IF(FIND("ABC",C1)>=0, "ABC", "Unknown")
    )

This statement worked for a source cell containing "XYZ" but would generate a "!Value" error otherwise. The find() function returns the position where it found a sub-string but would return an error if the sub-string could not be found. So I have to test the return value of the function instead of the position, resulting in a more cluttered statement:

=IF(ISNUMBER(FIND("XYZ",C1)), "XYZ",
    IF(ISNUMBER(FIND("ABC",C1)), "ABC", "Unknown")
    )

It's a rather annoying gotcha because returning an error is different from the VBA's InStr() function, which returns 0 when the sub-string can't be found.