Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Then assign value "Green"

Else if the string in cell A1 contains "xyz" as part of the string

Then assign value "Yellow"

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.

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")
Say P7 is a Cell then you can use the following Syntex to check the value of the cell and assign appropriate value to another cell based on this following nested if:
=IF(P7=0,200,IF(P7=1,100,IF(P7=2,25,IF(P7=3,10,IF((P7=4),5,0)))))
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.