How to SEARCH for more than one word/text/phrase?

[Eloise T]

The formula in Column L is searching in Column J for three words, OSS, Swap, and Unit. If ANY of them are in Column J AND Column H = 50, then put 50 in Column L. So far it works for OSS but not Swap or Unit. What is my error in logic? Thanks.

Spreadsheet attached for clarification.

=IF(AND(H2=50,IFERROR(SEARCH({"*OSS*","*Swap*","*Unit*"},J2)>0,0)),50)

 

[KenU]

The spreadsheet did not get uploaded, but perhaps this will work (untested). Change your formula to what is shown below:

=IF(AND(H2=50,SUMPRODUCT(IFERROR(SEARCH({"*OSS*","*Swap*","*Unit*"},J2),0))),50)

Using SEARCH with an array will produce an array result, so I am using SUMPRODUCT to produce a single numeric value instead (this also relies on your existing IFERROR to return a zero when there is no match).

The second change is simply removal of the ">0" test and adding a closing parenthesis for SUMPRODUCT. The AND function only needs a boolean (TRUE or FALSE) result, but any value other than zero is considered TRUE.

I hope that helps,
Ken

 

[Villalobos]

one more option...

=IF(AND(H2=50,IFERROR(IF(ISNUMBER(MATCH(9,99E+307,SEARCH({"*OSS*","*Swap*","*Unit*"},J2))),1,)>0,0)),50,0)

 

[Chihiro]

Same principle as above 2 formula,
=IF(SUMPRODUCT((H4=50)*ISNUMBER(SEARCH({"*OSS*","*Swap*","*Unit*"},J4)))>0,50,FALSE)

 

[bosco_yip]

Or...........

=(H2=50)*OR(COUNTIF(J2,{"oss","swap","unit"})>0)*50

Regards

 

[Eloise T]

The spreadsheet did not get uploaded, but perhaps this will work (untested). Change your formula to what is shown below:

=IF(AND(H2=50,SUMPRODUCT(IFERROR(SEARCH({"*OSS*","*Swap*","*Unit*"},J2),0))),50)

Using SEARCH with an array will produce an array result, so I am using SUMPRODUCT to produce a single numeric value instead (this also relies on your existing IFERROR to return a zero when there is no match).

The second change is simply removal of the ">0" test and adding a closing parenthesis for SUMPRODUCT. The AND function only needs a boolean (TRUE or FALSE) result, but any value other than zero is considered TRUE.

I hope that helps,
Ken

THANK YOU!

 

[Eloise T]

one more option...

=IF(AND(H2=50,IFERROR(IF(ISNUMBER(MATCH(9,99E+307,SEARCH({"*OSS*","*Swap*","*Unit*"},J2))),1,)>0,0)),50,0)

THANK YOU!

 

[Eloise T]

Same principle as above 2 formula,
=IF(SUMPRODUCT((H4=50)*ISNUMBER(SEARCH({"*OSS*","*Swap*","*Unit*"},J4)))>0,50,FALSE)

THANK YOU!

 

[Eloise T]

Or...........

=(H2=50)*OR(COUNTIF(J2,{"oss","swap","unit"})>0)*50

Regards

THANK YOU!

 

[Ramesh Deo]

Hi Eloise,

Here is easiest way to do the same if you like this.

=IF(AND(OR(J2={"oss","swap","unit"}),H2=50),50,"")

 

[shrivallabha]

Hi Eloise,

Here is easiest way to do the same if you like this.

=IF(AND(OR(J2={"oss","swap","unit"}),H2=50),50,"")

There probably is reason behind using SEARCH as the column name is Comment.
=AND(H2=50,COUNT(SEARCH({"oss","swap","unit"},J2)))*50

 

[Ramesh Deo]

There probably is reason behind using SEARCH as the column name is Comment.
=AND(H2=50,COUNT(SEARCH({"oss","swap","unit"},J2)))*50

Yes, Shrivallabha. You are right.Thanks!!