CODEWITHSUNDEEP
excelexcel-formula
user5548803
user5548803

Reputation:

Formual checking two cells with Search

I am trying to figure out a formula that will check two things in two cells that has SEARCH in it. I have made this and does not work. I changed AND to OR and it is working and giving me a response of 1 (i.e. I ensured the data in the other workbooks has the data to test this. What am I missing?

=IF(AND(ISNUMBER(SEARCH({"attach*","restart","shut down","shutdown","reboot","out of rotation","power off","ripple start","kill","break mirror","stop* service","service stop*"},'RFC Data'!AP4)),'RFC Data'!L4="No"),1,"")

Upvotes: 1

Views: 38

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

Wrap the search in sumproduct and test if the return is greater than 0:

=IF(AND(SUMPRODUCT(--(ISNUMBER(SEARCH({"attach*","restart","shut down","shutdown","reboot","out of rotation","power off","ripple start","kill","break mirror","stop* service","service stop*"},'RFC Data'!AP4))))>0,'RFC Data'!L4="No"),1,"")

Upvotes: 1

Related Questions