Need Help With A Find Formula

T

toy4x4

Ok, this works to start off:

=IF(FIND("Smith",B1),"Mine","Not Mine")

Problem is when Smith is not in the name I get #VALUE instead of "Not
Mine".

Then how to do nester ORs for Smith, Jones, etc.

Cause this doesn't work:

=IF(OR(FIND("Smith",B1),FIND("Jones",B1)),"Mine","Not Mine")

I just get #VALUE
 
J

JMB

try changing the order and testing for an error. I threw in the Proper
function to make sure the case is the same, otherwise I believe you will get
an error.

=IF(AND(ISERROR(FIND("Smith",PROPER(B1))),ISERROR(FIND("Jones",PROPER(B1)))),"Not Mine","Mine")
 
D

Domenic

Try...

=IF(OR(ISNUMBER(FIND({"Smith","Jones"},B1))),"Mine","Not Mine")

OR

=IF(OR(ISNUMBER(FIND(A1:A2,B1))),"Mine","Not Mine")

OR

=IF(B1<>"",IF(OR(ISNUMBER(FIND(A1:A2,B1))),"Mine","Not Mine"),"")

....where A1:A2 contains your list of names. The last two formulas need
to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Also, the
last formula contains an additional IF statement to return a blank when
B1 is empty.

Hope this helps!
 
Top