[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Nested If Find

Posted on 2011-02-23
9
Medium Priority
?
306 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
9 Comments
 
LVL 16

Accepted Solution

by:
sjklein42 earned 1000 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

834 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