Forum Discussion
If Or Statement, Cell contains specific text
Hello - I am trying to write a formula that says If Name contains Fred1, Fred2, or if Column B says Blue, or Column C says Leave, then Include (True) or Exclude (False). For example: Name Sp...
=(ISNUMBER(SEARCH("Fred1*",name_range)) + (spec_range = "Blue") + (leave_range = "Leave") )> 0
As dynamic array formula, if using Microsoft 365
- Replace SEARCH with FIND if case sensitivity is needed. Also adjust wildcard (*/?) placement according to your need
- `+` is used for OR operations and `*` used for AND operations, with TRUE = 1 and FALSE = 0
Think I'm using this wrong, as I'm returning #VALUE when I drag it down to the rest of the cells. Formula being: =(IsNumber(Search("Fred1",A2)+(A2="Leave"))>0
John23785 Assuming you have Microsoft 365, you need to enter the formula in the first cell only - no need to fill-down. Here's an example based on you sample data. Please let me know which version of Excel you're using if this doesn't work.
In case the screenshot it not visible, here's a sample:
=(ISNUMBER(SEARCH("Fred1*",A2:A5)) + (B2:B5 = "Blue") + (C2:C5 = "Leave") )> 0- Thank you!!
- Thanks Hans!
