Error in Formula When Comparing Multiple Criteria

Question

I have several columns of data that need to meet certain criteria to be included in my spreadsheet. I have a column named "Removal" that checks this criteria and returns a "Yes" or "No" depending on the values.

If one of the following criteria is true, then a "Yes" needs to be returned.

The criteria:

• Expiration Date cannot be 12/30/2055
• The New Title column cannot include titles with "Expired" or "6 Months"
• The Exclusion column cannot be equal to "Yes"

The applicable columns:

• Expiration Date - Column D
• New Title - Column E
• Exclusion - Column F

The formula I wrote:

=IF(OR(ISNUMBER(SEARCH("Expired", E2))<>0, F2="yes", D2>DATEVALUE("12/30/2055"), ISNUMBER(SEARCH("6 Months", E2))<>0), "Yes", "No")

After running this, I get "Yes" despite that there are some where "No" is the expected value.

Looking into the formula checker, it appears that the ISNUMBER(SEARCH()) function is throwing it off.

In the below example, the "New Title" is "$100 Off Rebate", which does not include "Expired" or "6 Months", so it should return FALSE but it returns TRUE when looking into the OR() function

I feel like the answer is right in front of me but I cannot figure it out.

Answer 1

Hi @Renee Crozier,

The problem is the part <>0 after the ISNUMBER(SEARCH()) part. In Excel, the logical value TRUE/FALSE is not being treated as numeric value, so when you compare TRUE/FALSE with 0, both of them will return TRUE. Basically, if you add <>0, even if the New Title is "$100 Off Rebate" or anything else, it always returns TRUE.

Hope my sharing may be helpful for you to move forward smoothly. If you have any questions or need any further clarification, please leave a comment here and I will be happy to continue to help you.  

Thank you for your patience and understanding. I'm looking forward to your reply. 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.