• Status: Solved
• Priority: Medium
• Security: Public
• Views: 310

# Nested If Find

is it possible to do a nested if find or search on a cell ?

e.g =IF(FIND("Ross",A1),"TRUE",IF(FIND("Turner",A1),"True","False")
0
Ross Turner
1 Solution

Commented:
FIND does not return True/False, you need to use ISERROR to check whether it succeeded or not:

``````=IF(NOT(ISERROR(FIND("Ross",A1))),"TRUE",(IF(NOT(ISERROR(FIND("Turner",A1))),"TRUE","FALSE")))
``````
0

Commented:
=IF(AND(ISERROR(SEARCH("Ross",A1)),ISERROR(SEARCH("Turner",A1))),"FALSE","TRUE")

for not case-sensitive

=IF(AND(ISERROR(FIND("Ross",A1)),ISERROR(FIND("Turner",A1))),"FALSE","TRUE")

for case-sensitive
0

Commented:
=IF(AND(ISERROR(SEARCH("Ross",A1)),ISERROR(SEARCH("Turner",A1))),"FALSE","TRUE")

for not case-sensitive

=IF(AND(ISERROR(FIND("Ross",A1)),ISERROR(FIND("Turner",A1))),"FALSE","TRUE")

for case-sensitive
0

Commented:
If you want TRUE if either "Ross" or "Turner" is in A1 try

=COUNT(SEARCH({"Ross","Turner",A1))>0

You can put that in an IF if you want e.g.

=IF(COUNT(SEARCH({"Ross","Turner",A1))>0,"x","y")

regards, barry
0

Commented:
Sorry, I missed out a }, try like this

=COUNT(SEARCH({"Ross","Turner"},A1))>0

or the IF like this

=IF(COUNT(SEARCH({"Ross","Turner"},A1))>0,"x","y")

regards, barry

0

Commented:
Good one Barry, why doesn't this "=COUNT(SEARCH({"Ross","Turner"},A1))" throw out an error when the values are not found, whereas "=SEARCH({"Ross","Turner"},A1)" does?
0

Commented:
Hello Runrigger

COUNT just ignores errors so if Ross is found but Turner isn't the SEARCH function returns an array something like this

{10,#VALUE!}

COUNT just counts numbers so in that case the result of the COUNT function is 1, obviously any number > 0 indicates a match with a least one of the "names". I like this approach because you can easily extend it to count many names, you can even use a cell range that contains the names in place of {"Ross","Turner"}.....but that will make the formula into an "array"......

regards, barry
0

Commented:
ah, ah, got it. I should have realised, COUNT is not a function that I use very often!
0

Management Information Support AnalystAuthor Commented:
That worked beautifully !!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.