Searching cells for certain words

[nclacs99]

Hi all, I have a column with descriptions in it. Based on the words in that column, I would like a formula to provide either the value in the next column to the left of the descriptions column or a 0. Here is an example of the formula that I tried. In this example, I would like the formula to return a 0 if EITHER the word "blah" OR "meh" is found in that cell. =IF(AND(ISNUMBER(SEARCH("qtr",H11)),ISNUMBER(SEARCH("blah",H11))),0,IF(ISNUMBER(SEARCH("qtr",H11)),G11,IF(AND(ISNUMBER(SEARCH("qtr",H11)),ISNUMBER(SEARCH("meh",H11))),0,IF(ISNUMBER(SEARCH("qtr",H11)),G11,0)))). Right now, the formula is still returning the G11 value even though the word "meh" appears in column "H". Thanks for any help!!

 

[ParamRay]

.
.

=IF(OR(ISNUMBER(SEARCH("blah",H11)),ISNUMBER(SEARCH("meh",H11))),0,G11)

 

[nclacs99]

Actually, I failed to mention that the formula should be looking for a certain word (or words) in that same column. For example, in the original post, I was looking for "qtr". So the formula should look for the letters "qtr" and record the value in G11, but if it sees "blah" OR "meh", then it should return a "0" value.

 

[ParamRay]

.
.

So what you're saying is:

If cell H11 contains text "qtr" then formula returns value of G11; unless cell H11 contains text "blah" or "meh", in which case formula returns value 0.

What, however, should happen if cell H11 does not contain text "qtr"?

 

[nclacs99]

If cell H11 does not contain text "qtr" then the formula should return a 0.

 

[ParamRay]

.
.

=IF(AND(ISNUMBER(SEARCH("qtr",H11)),NOT(ISNUMBER(SEARCH("blah",H11))),NOT(ISNUMBER(SEARCH("meh",H11)))),G11,0)

 

[Aladin Akyurek]

If cell H11 does not contain text "qtr" then the formula should return a 0.

Something like:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH("qtr",H11),G11),0)

 

[nclacs99]

.
.

=IF(AND(ISNUMBER(SEARCH("qtr",H11)),NOT(ISNUMBER(SEARCH("blah",H11))),NOT(ISNUMBER(SEARCH("meh",H11)))),G11,0)

Perfect! Thanks gpeacock, this worked great.

 

[Aladin Akyurek]

Would you post some examples along with the expected results in order to convince?