If/Then Formulas with text

**Kokoni** · 11-27-2009, 12:24 PM

I haven't been using Excel long, still learning alot.

I want to create a formula that returns a true/false answer based on text. I've figured out how to do a simple version of this with "=IF(F3="Q", 10, 5)". What I really need is a formula with multiple conditions so that a set of five-six text entrys (KS, KSV, DS, etc) returns one response, and another set (KQ, KQS, DQ, etc) returns another. I don't know if it's possible to ask the program to search within a text entry for one character (S vs. Q), or if the formula would just need to have each of the entrys of both sets in it.

Is this formula possible?

I've looked up some multiple conditions formulas, but as they all involve numbers I'm not sure how to rewrite them.

**DonkeyOte** · 11-27-2009, 12:28 PM

If, as it seems, the S & Q are mutually exclusive then perhaps:

=IF(ISNUMBER(SEARCH("Q",F3)),10,5)

(if you wish to make the test case sensitive (ie Q <> q) then use FIND rather than SEARCH)

**Kokoni** · 11-27-2009, 01:29 PM

Huzzah! It works! Thank you so much!

**Fcroft** · 12-01-2009, 01:09 PM

I'm just going through threads looking for new formulas I don't currently use and saw the ISNUMBER. Granted its very self explanitory, but why use it in this case? Couldn't you simply have used

=IF(SEARCH("Q",F3),10,5)

and gotten the same results?

**DonkeyOte** · 12-01-2009, 01:11 PM

No. If Q does not exist in the string a #VALUE! error will be returned hence the subsequent ISNUMBER test (ie to evaluate Error status - not the position of Q itself should it exist)
(always best to test)