IF - ELSE IF - ELSE Structure in Excel

Question

Requirement : If the string in cell A1 contains "abc" as part of the string Then assign value "Green" Else if the string in cell A1 contains "xyz" as part of the string Then assign value "Yellow" Else Assign value "Red" I tried this : `=IF(FIND("~",SUBSTITUTE(A1,"abc","~",1))<>"#VALUE!", "Green", IF(FIND("~",SUBSTITUTE(A1,"xyz","~",1))<>"#VALUE!", "Yellow", "Red"))` It works if first IF is True in other cases it gives #VALUE! as output. Can't figure out whats wrong.

Accepted Answer

When `FIND` returns `#VALUE!`, it is an error, not a string, so you can't compare `FIND(...)` with `"#VALUE!"`, you need to check if `FIND` returns an error with `ISERROR`. Also `FIND` can work on multiple characters.

So a simplified and working version of your formula would be:

```
=IF(ISERROR(FIND("abc",A1))=FALSE, "Green", IF(ISERROR(FIND("xyz",A1))=FALSE, "Yellow", "Red"))
```

Or, to remove the double negations:

```
=IF(ISERROR(FIND("abc",A1)), IF(ISERROR(FIND("xyz",A1)), "Red", "Yellow"),"Green")
```

IF - ELSE IF - ELSE Structure in Excel

Solution

Problem