Solved

Nested If Find

Posted on 2011-02-23
9
293 Views
Last Modified: 2012-08-14
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
Comment
Question by:Ross Turner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 16

Accepted Solution

by:
sjklein42 earned 250 total points
ID: 34961766
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
 
LVL 11

Expert Comment

by:Runrigger
ID: 34961768
=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
 
LVL 11

Expert Comment

by:Runrigger
ID: 34961778
=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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 50

Expert Comment

by:barry houdini
ID: 34961855
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 34961892
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
 
LVL 11

Expert Comment

by:Runrigger
ID: 34961910
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 34962035
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
 
LVL 11

Expert Comment

by:Runrigger
ID: 34962056
ah, ah, got it. I should have realised, COUNT is not a function that I use very often!
0
 
LVL 7

Author Closing Comment

by:Ross Turner
ID: 34962490
That worked beautifully !!!!
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question