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

x
Solved

# Nested If Find

Posted on 2011-02-23
Medium Priority
306 Views
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
Question by:Ross Turner

LVL 16

Accepted Solution

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")))
``````
0

LVL 11

Expert Comment

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

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

LVL 50

Expert Comment

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

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

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

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

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

ID: 34962490
That worked beautifully !!!!
0

## Featured Post

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…
###### Suggested Courses
Course of the Month18 days, 23 hours left to enroll