Find & Search Function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

drvortex - 15 Jun 2006 22:15 GMT

Hello All,

I'm back and needing some assistance. I tried to do a Search on thi
to see if there was already an answer but wouldn't work. So here w
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 tex
result based off of a particular text string. May sound confusing bu
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 i
true then will look into C9 for "TS" and if that is true, will displa
YY in the cell. This part of the formula works.

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

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

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

Thanks,

Jaso

Reply to this Message

Die_Another_Day - 15 Jun 2006 22:35 GMT

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

> Hello All,
>
[quoted text clipped - 34 lines]
> drvortex's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15896
> View this thread: http://www.excelforum.com/showthread.php?threadid=552484

Reply to this Message

Elkar - 15 Jun 2006 22:36 GMT

Try this:

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

HTH,
Elkar

> Hello All,
>
[quoted text clipped - 28 lines]
>
> Jason

Reply to this Message

drvortex - 16 Jun 2006 15:23 GMT

Elkar Wrote:

> Try this:
>
[quoted text clipped - 3 lines]
> 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!!!!!

Signature

drvortex

Reply to this Message

drvortex - 16 Jun 2006 15:24 GMT

QUOTE=Elkar]Try this:

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

HTH,
Elkar

>[/color]

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

Reply to this Message

Elkar - 16 Jun 2006 17:31 GMT

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

> QUOTE=Elkar]Try this:
>
[quoted text clipped - 9 lines]
> finish up the charts and begin all the forecast verification metrics. My
> boss is going to be happy. Sweet!!!!!

Reply to this Message

drvortex - 16 Jun 2006 20:34 GMT

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

Signature

drvortex

Reply to this Message