CODEWITHSUNDEEP
excelfunctionif-statementsearch
ladymrt
ladymrt

Reputation: 95

Look for a word in a set of cells

I am trying to create a cell that gives a pass/fail result based on the contents of D11:D15. If even one of the cells in that range have the value of "FAIL", I want the cell above (that the formula is going into) to read "FAILED". If they are all "PASS", I would like it to say "PASSED". Sounds simple enough, but I'm having issues

=IF((ISNUMBER(SEARCH("FAIL",D11:D15))),"FAILED","PASSED")

This formula is returning passed no matter what. Even if there are fails in the range. What am i missing here?

Upvotes: 2

Views: 37

Answers (1)

Scott Craner
Scott Craner

Reputation: 152465

You formula will only return the result of the first cell D11.

Use COUNTIF with wildcards:

=IF(COUNTIF(D11:D15,"*FAIL*"),"FAILED","PASSED")

Or if you really want to use Search then you need to wrap the ISNUMBER in SUMPRODUCT:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH("FAIL",D11:D15)))),"FAILED","PASSED")

Upvotes: 3

Related Questions