Menu
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.
798
Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false. For example: =IF(A2>B2,"Over Budget","OK") =IF(A2=B2,B4-A4,"")
Use the IF function along with AND, OR and NOT to perform multiple evaluations if conditions are True or False. The condition you want to test. The value that you want returned if the result of logical_test is TRUE. The value that you want returned if the result of logical_test is FALSE.
IF is one of the most popular features in Excel. Sometimes in a spreadsheet, something should be done if certain conditions are in place. What is known as an “if” statement can be solved with an “if-then” statement in Excel. The principle is the same: if a value is true, then do something, otherwise do something else.
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 you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
