Multiple Condition IF/OR Statement with TEXT

[spochedly]

I am trying to return “Address” if late entry code does NOTcontain “yes” (preferably not case sensitive), OR if the very last column,outsource PO # = 0. If neither of those conditions are met, I would like toreturn “Clear.” I have responses for all based on this formula:

=IF(OR(AM51 = 0, C51 <> "yes"),"Address", "Clear")
However, it is returning “Address” to some that should beclear. I think this is because I need to do either a FIND or SEARCH function tosupport the OR clause. Can anyone please advise?




[TABLE="width: 742"]
<colgroup><col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"><colgroup><col width="132" style="width: 99pt; mso-width-source: userset; mso-width-alt: 4827;"><colgroup><col width="57" style="width: 43pt; mso-width-source: userset; mso-width-alt: 2084;"><colgroup><col width="64" style="width: 48pt;"><colgroup><col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"><colgroup><col width="85" style="width: 64pt; mso-width-source: userset; mso-width-alt: 3108;"><colgroup><col width="147" style="width: 110pt; mso-width-source: userset; mso-width-alt: 5376;"><colgroup><col width="53" style="width: 40pt; mso-width-source: userset; mso-width-alt: 1938;"><colgroup><col width="142" style="width: 107pt; mso-width-source: userset; mso-width-alt: 5193;"><colgroup><col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;"><colgroup><col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;"><tbody>[TR]
[TD="width: 71, bgcolor: transparent"]Date Entered
[/TD]
[TD="width: 132, bgcolor: transparent"]Late Entry Code
[/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 71, bgcolor: transparent"]Stock Location[/TD]
[TD="width: 85, bgcolor: transparent"]Order Number[/TD]
[TD="width: 147, bgcolor: transparent"]Bill-to Name
[/TD]
[TD="width: 53, bgcolor: transparent"]qty open[/TD]
[TD="width: 142, bgcolor: transparent"]Part Number[/TD]
[TD="width: 92, bgcolor: transparent"]Anticipated Ship Date[/TD]
[TD="width: 74, bgcolor: transparent"]Outsource PO #[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 742"]
<colgroup><col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"><colgroup><col width="132" style="width: 99pt; mso-width-source: userset; mso-width-alt: 4827;"><colgroup><col width="57" style="width: 43pt; mso-width-source: userset; mso-width-alt: 2084;"><colgroup><col width="64" style="width: 48pt;"><colgroup><col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"><colgroup><col width="85" style="width: 64pt; mso-width-source: userset; mso-width-alt: 3108;"><colgroup><col width="147" style="width: 110pt; mso-width-source: userset; mso-width-alt: 5376;"><colgroup><col width="53" style="width: 40pt; mso-width-source: userset; mso-width-alt: 1938;"><colgroup><col width="142" style="width: 107pt; mso-width-source: userset; mso-width-alt: 5193;"><colgroup><col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;"><colgroup><col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;"><tbody>[TR]
[TD="width: 71, bgcolor: transparent, align: right"]1/21/201 9
[/TD]
[TD="width: 132, bgcolor: transparent"]29908 29909 YES
[/TD]
[TD="width: 57, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]Address[/TD]
[TD="width: 71, bgcolor: transparent"]DFTDN2[/TD]
[TD="width: 85, bgcolor: transparent"]498362
[/TD]
[TD="width: 147, bgcolor: transparent"]NO CHARGE/REPLACEMENTS
[/TD]
[TD="width: 53, bgcolor: transparent"] 2
[/TD]
[TD="width: 142, bgcolor: transparent"]APRS18UNIT2[/TD]
[TD="width: 92, bgcolor: transparent, align: right"]1/31/2019
[/TD]
[TD="width: 74, bgcolor: transparent"]0
[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlanY]

try

=IF(OR(AM51 = 0, NOT(ISNUMBER(SEARCH("yes",C51)))),"Address", "Clear")