Find & Search Function
 

Hello All,

I'm back and needing some assistance. I tried to do a Search on this
to see if there was already an answer but wouldn't work. So here we
go.

What I'm trying to do is the following:

I want to compare two cells w/ text in them and want to return a text
result based off of a particular text string. May sound confusing but
will try and draw it.

The equation I have so far is

=IF(SEARCH("TS",B9), IF(SEARCH("TS",C9),"YY","YN"),
IF(SEARCH("TS",C9),"NY","NN"))

What this is doing is looking into cell B9 to find "TS", if that is
true then will look into C9 for "TS" and if that is true, will display
YY in the cell. This part of the formula works.

However, when I put something other than a TS or leave it blank in B9
or C9...the result is #VALUE!

The problem that I'm having is that I need the result to be YN or NY or
NN. Anything w/ a false result won't work.

Hope you understand what I'm asking...any assistance will be good.

Thanks,

Jason


--
drvortex
------------------------------------------------------------------------
drvortex's Profile: http://www.excelforum.com/member.php...o&userid=15896
View this thread: http://www.excelforum.com/showthread...hreadid=552484

Find & Search Function
 

I dealt with this exact problem earlier this week. Use the is error
function to filter this out
if(iserror(Search("TS",B9)),"Blah","BlahBlah")
Post back if that doesn't get you where you need to be.

HTH

Die_Another_Day
drvortex wrote:
Hello All,

I'm back and needing some assistance. I tried to do a Search on this
to see if there was already an answer but wouldn't work. So here we
go.

What I'm trying to do is the following:

I want to compare two cells w/ text in them and want to return a text
result based off of a particular text string. May sound confusing but
will try and draw it.

The equation I have so far is

=IF(SEARCH("TS",B9), IF(SEARCH("TS",C9),"YY","YN"),
IF(SEARCH("TS",C9),"NY","NN"))

What this is doing is looking into cell B9 to find "TS", if that is
true then will look into C9 for "TS" and if that is true, will display
YY in the cell. This part of the formula works.

However, when I put something other than a TS or leave it blank in B9
or C9...the result is #VALUE!

The problem that I'm having is that I need the result to be YN or NY or
NN. Anything w/ a false result won't work.

Hope you understand what I'm asking...any assistance will be good.

Thanks,

Jason


--
drvortex
------------------------------------------------------------------------
drvortex's Profile: http://www.excelforum.com/member.php...o&userid=15896
View this thread: http://www.excelforum.com/showthread...hreadid=552484

Find & Search Function
 

Try this:

=IF(ISERROR(SEARCH("TS",B9)),"N","Y")&IF(ISERROR(S EARCH("TS",C9)),"N","Y")

HTH,
Elkar


"drvortex" wrote:


Hello All,

I'm back and needing some assistance. I tried to do a Search on this
to see if there was already an answer but wouldn't work. So here we
go.

What I'm trying to do is the following:

I want to compare two cells w/ text in them and want to return a text
result based off of a particular text string. May sound confusing but
will try and draw it.

The equation I have so far is

=IF(SEARCH("TS",B9), IF(SEARCH("TS",C9),"YY","YN"),
IF(SEARCH("TS",C9),"NY","NN"))

What this is doing is looking into cell B9 to find "TS", if that is
true then will look into C9 for "TS" and if that is true, will display
YY in the cell. This part of the formula works.

However, when I put something other than a TS or leave it blank in B9
or C9...the result is #VALUE!

The problem that I'm having is that I need the result to be YN or NY or
NN. Anything w/ a false result won't work.

Hope you understand what I'm asking...any assistance will be good.

Thanks,

Jason


--
drvortex
------------------------------------------------------------------------
drvortex's Profile: http://www.excelforum.com/member.php...o&userid=15896
View this thread: http://www.excelforum.com/showthread...hreadid=552484

Find & Search Function
 

Elkar Wrote:
Try this:

=IF(ISERROR(SEARCH("TS",B9)),"N","Y")&IF(ISERROR(S EARCH("TS",C9)),"N","Y")

HTH,
Elkar
[/color]

I verified and this is what I needed. Could you do me one favor and
explain to me what this formula means. Explain the process of the
formula. Thanks so much. This has saved alot of time and now I can
finish up the charts and begin all the forecast verification metrics.
My boss is going to be happy. Sweet!!!!!


--
drvortex
------------------------------------------------------------------------
drvortex's Profile: http://www.excelforum.com/member.php...o&userid=15896
View this thread: http://www.excelforum.com/showthread...hreadid=552484

Find & Search Function
 

QUOTE=Elkar]Try this:

=IF(ISERROR(SEARCH("TS",B9)),"N","Y")&IF(ISERROR(S EARCH("TS",C9)),"N","Y")

HTH,
Elkar
[/color]

I verified and this is what I needed. Could you do me one favor and
explain to me what this formula means. Explain the process of the
formula. Thanks so much. This has saved alot of time and now I can
finish up the charts and begin all the forecast verification metrics. My
boss is going to be happy. Sweet!!!!!


--
drvortex
------------------------------------------------------------------------
drvortex's Profile: http://www.excelforum.com/member.php...o&userid=15896
View this thread: http://www.excelforum.com/showthread...hreadid=552484

Find & Search Function
 

This formula is basically just two IF statements joined together (by the &
sign). Breaking it down into individual elements, we'll start inside and
work our way out.

First off, the SEARCH function looks inside cell B9 for the text string
"TS". If "TS" is found, then it returns a number indicating the starting
position within the cell (the number isn't really important). If not found,
it returns an error (this is the important part).

This is where the ISERROR function comes in. It looks at the results of the
SEARCH function and asks if it returns an error or not. ISERROR then
evaluates to a value of TRUE or FALSE.

Now, we get to the IF Statement. Since ISERROR returns either TRUE or
FALSE, that is all the IF function needs to work with. If TRUE (there is an
error), then return the value "N", if FALSE (there is not an error) then
return the value "Y".

The second half of the formula is identical except that it searches C9
instead of B9.

The & symbol in the middle just tells excel to take the results of each
formula and concatenate (join) them together.

HTH,
Elkar

"drvortex" wrote:


QUOTE=Elkar]Try this:

=IF(ISERROR(SEARCH("TS",B9)),"N","Y")&IF(ISERROR(S EARCH("TS",C9)),"N","Y")

HTH,
Elkar


I verified and this is what I needed. Could you do me one favor and
explain to me what this formula means. Explain the process of the
formula. Thanks so much. This has saved alot of time and now I can
finish up the charts and begin all the forecast verification metrics. My
boss is going to be happy. Sweet!!!!!


--
drvortex
------------------------------------------------------------------------
drvortex's Profile: http://www.excelforum.com/member.php...o&userid=15896
View this thread: http://www.excelforum.com/showthread...hreadid=552484

[/color]

Find & Search Function
 

Awesome....I completely understand the madness of the formula. Thanks
so much.


--
drvortex
------------------------------------------------------------------------
drvortex's Profile: http://www.excelforum.com/member.php...o&userid=15896
View this thread: http://www.excelforum.com/showthread...hreadid=552484