Help with solving this if statement which uses a search for text s

F

forest8

Hi

I am using Excel 2007 and trying to fix this formula.

In exvcel, there are several questions which could be used in the result of
the same cell.

This is what I need fixed:

=IF(OR(ISNUMBER(SEARCH("go",YS!E13)),(ISNUMBER(SEARCH("D",YS!E14)),(ISNUMBER(SEARCH("F",YS!E14)))))),"Yes","Apple")

What I'm trying to do is put multiple conditions on this cell.YS!E13
contains the word "GO", or YS contains either a "D" or "F", then the cell
should return "YES", if not, it should return "Apple".

I can't get this formula to work with this complex equation.

Thank you
 
J

Jacob Skaria

Try the belo
=IF(COUNT(SEARCH("GO",YS!E13),SEARCH("D",YS!E13),SEARCH("F",YS!E13)),"Yes","Apple")

If this post helps click Yes
 
T

T. Valko

multiple conditions on this cell.YS!E13
=IF(OR(ISNUMBER(SEARCH("go",YS!E13)),(ISNUMBER(SEARCH("D",YS!E14)),(ISNUMBER(SEARCH("F",YS!E14)))))),"Yes","Apple")

Your formula is referencing 2 cells, E13 and E14.

Try it like this:

=IF(COUNT(SEARCH({"go","D","F"},YS!E13)),"Yes","Apple")
 
J

Jacob Skaria

Forgot to mention that when you are searching multiple cells...you need to
have mutiple SEARCH conditions within COUNT..for E13, E14 etc;....

=IF(COUNT(SEARCH("GO",YS!E13),SEARCH("D",YS!E14),SEARCH("F",YS!E14)),"Yes","Apple")


If this post helps click Yes
 
T

T. Valko

Forgot to mention that when you are searching
multiple cells...you need to have mutiple SEARCH
conditions within COUNT..for E13, E14 etc;....
=IF(COUNT(SEARCH("GO",YS!E13),SEARCH("D",YS!E14),SEARCH("F",YS!E14)),"Yes","Apple")

Array entered** :

=IF(COUNT(SEARCH({"GO","D","F"},YS!E13:E14)),"Yes","Apple")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
J

Jacob Skaria

What about? (non-array entered)

=IF(COUNT(SEARCH({"GO","D","F"},E13&E14)),"Yes","Apple")

If this post helps click Yes
 
T

T. Valko

What about? (non-array entered)
=IF(COUNT(SEARCH({"GO","D","F"},E13&E14)),"Yes","Apple")

Yeah, that's a possibility. It depends on the data.

What would happen if this was the data:

E13 = G
E14 = O
 
J

Jacob Skaria

True..That depends on the data..Thanks Biff. The point here is that for
multiple range of cells we can use the array entered formula
 
T

T. Valko

No, the point here is that I'm messing with you! <g>

If you don't want to use an array formula, and since we don't know what the
possible cell entries could be, something like this should be fairly safe:

=IF(COUNT(SEARCH({"GO","D","F"},E13&"^^"&E14)),"Yes","Apple")
 
F

forest8

These responses were extremely helpful. And they both are viable in my
spreadhseet.

In answer to the posts re multiple cell references, my original equation
references four different cells.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top