CODEWITHSUNDEEP
excelopenoffice-calc
user3292026
user3292026

Reputation: 80

calc/excel if find text in two cells and output text

This is probably too simple but I just can't find my way around it. I have to cells 

  A1              B1
high ground     low water

So I need a formula that states that if cell A1 contains "high ground" and cell B1 contains the word "water" output "OK" otherwise "no ok"

Thanks in advance

Upvotes: 1

Views: 1904

Answers (4)

MauricioTL
MauricioTL

Reputation: 443

Next formula is working for me:

=if(AND(A2="high ground",(B2=SEARCH("water",B2,1))>0),"ok","not ok")

I'm supposing cell A2 contains high groud, and cell B2 contains low water.

In the if statement there are two conditions: if cell A2= High ground and if cell b2 contains water then Ok else not ok.

Hope it works for you!!

Sorry, next is the right one (whithout HALLAR, instead SEARCH):

please try next:

=IF(AND(A1="high ground",(B1=SEARCH("water",B1,1))>0),"ok","not ok"

Upvotes: 1

tohuwawohu
tohuwawohu

Reputation: 13608

For OpenOffice / LibreOffice Calc, the following function should work:

=IF(AND(A1 = "high ground";NOT(ISERROR(SEARCH("water";B1;1))));"OK";"not OK")

or split up on multiple lines with comments:

=IF(                                   # First function: result will depend on condition
    AND(                               # Condition: two sub-conditions have to be TRUE
        A1 = "high ground";            # First subcondition: simple equality check
        NOT(                           # Second subcondition
            ISERROR(                   # Check if the embededded function (SEARCH()) returns an error
                SEARCH("water";B1;1)   # Search B1 for substring. Will throw an error if there's no match in B! for string "water"
            )
        )
    );
    "OK";                              # Return value if condition is fulfilled;
    "not OK"                           # return value if on of the sub-conditions isn't fulfilled.
)

The NOT(ISERROR(SEARCH("water";B1;1))) is a little bit complicated. Since the SEARCH() function (as well as the FIND() function) throws an error if there's no match, you will have to handle that error, else the complete formula will return an error instead of the string "not ok". For error handling, i put the SEARCH() function into an ISERROR() function, translating the result of SEARCH() into a boolean value (TRUE or FALSE). Since we want a TRUE result if the search finds a match, we have to "invert" the output of ISERROR() using the NOT() function.

Searching for a substring could also be done using regular expressions, but this would require enabling regular expressions in the Calc Program Options; the proposed solution using ISERROR(SEARCH()) should work independently of that config setting.

Upvotes: 0

Matt
Matt

Reputation: 23

Sorry my answer before included a wildcard within an If statement and for some reason excel doesn't recognise them.

I have tested this one and it should work for you:

=IF(AND(A1="high ground",ISNUMBER(SEARCH("water",B1))),"OK","Not OK")

Upvotes: 0

Matt
Matt

Reputation: 23

Try this:

=if(AND(A1="high ground",B1="*water*"),"OK","Not OK")

Upvotes: 0

Related Questions