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 | Specification | Where |
| Fred1 | Blue | Leave |
| Fred2 | Blue | Stay |
| Fred3 | Yellow | Leave |
Hoping this makes sense, please let me know if otherwise. Thank you,
=(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
=OR(Name_cell="Fred1", Name_cell="Fred2", Specification_cell="Blue", Where_cell="Leave")
will return TRUE if any of the conditions is met, FALSE otherwise.
- Thanks Hans, now if "Name" cell contains Fred1 like Fred1LastName, how could I specify it for just Fred1?
