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

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
Asked:
Ross Turner
1 Solution
 
sjklein42Commented:
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")))

Open in new window

0
 
RunriggerCommented:
=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
 
RunriggerCommented:
=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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
barry houdiniCommented:
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
 
barry houdiniCommented:
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
 
RunriggerCommented:
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
 
barry houdiniCommented:
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
 
RunriggerCommented:
ah, ah, got it. I should have realised, COUNT is not a function that I use very often!
0
 
Ross TurnerManagement 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.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now