Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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] |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Function | Excel Discussion (Misc queries) | |||
Improve Excel Help Text - Make easier to Find Function Refs | Excel Worksheet Functions | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
Match Function Problem - Won't Find Certain Numbers | Excel Discussion (Misc queries) | |||
ISNULL function - can't find it | Excel Worksheet Functions |