# Searching cells for certain words

#### nclacs99

##### Board Regular
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!!

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### ParamRay

##### Well-known Member
.
.

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

#### nclacs99

##### Board Regular
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

##### Well-known Member
.
.

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

##### Board Regular

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

#### ParamRay

##### Well-known Member
.
.

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

##### MrExcel MVP

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

##### Board Regular
.
.

=IF(AND(ISNUMBER(SEARCH("qtr",H11)),NOT(ISNUMBER(SEARCH("blah",H11))),NOT(ISNUMBER(SEARCH("meh",H11)))),G11,0)
Perfect! Thanks gpeacock, this worked great.

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

Replies
6
Views
99
Replies
4
Views
184
Replies
5
Views
213
Replies
3
Views
74
Replies
8
Views
179