If you ever need to know whether a particular word is repeated in a cell (for eg: a file extension or telephone number or name of the customer) here is how you can find it.
=if(lower(cell-with-text)<>SUBSTITUTE(lower(cell-with-text),lower(word-to-check),"",2),"Yes","No")
We are using substitute() to replace the second occurance of a particular word and then compare this with original cell contents. If both are not equal, we know that the word is repeated.
For eg: =if(lower("all cats are dogs, but not all dogs are cats")<>SUBSTITUTE(lower("all cats are dogs, but not all dogs are cats"),lower("cat"),"",2),"Yes","No") would return “yes”
See related posts for more text processing using excel hacks.
One Response to “Excel IF Statement Two Conditions”
At first I thought you missed an opportunity to introduce the IFS and CHOOSE functions, but I see you have those (buried) in the "10 Advanced IF Tricks..." article.
Also, by adding a "Check my results" checkbox (I suggest cell Q3) and a couple conditional formatting rules to apply red/green fill, you could help the users check their formulas.