|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
October 2nd, 2006, 04:46 PM
posted to microsoft.public.excel.setup
|
|||
|
|||
SEARCH function within IF function
Excel 2003 SP2
I'm attempting to use the SEARCH function for a couple of strings in a cell. Specifically, I need to search for "Customer", "Vendor" and "Item". Seems simple but when I use the following IF statement, I get a #VALUE response on the FALSE condition. Here's the statement IF(Search("Customer",B2,1)0,"Yes","No") Columns==== B C D E Customer Master File =If(Search...) =if(Search....) =If(Search...) Vendor Master File etc etc etc Item Master File etc etc etc If Bxxxx has "Customer" I get the "Yes". However, if Bxxxx does NOT have "Customer" I get the #Value response. I've tried several iterations of nested IFs and other SEARCHes but I'm still getting #VALUE. My desired result is if Bxxxx has "Customer" then "Yes", if Bxxxx has "Vendor" then "No" or blank. I'm essentially establishing columns C, D and E to be Customer, Vendor and Item Related. Hence, if Bxxxx contains "Customer" then Cxxxx would be "Yes". If Bxxxx contains "Vendor" then Dxxxx would be "Yes" and if Bxxxx contains "Item" then Exxxx would be "Yes". Otherwise, I'd like to see a "No" or a blank (""). What solutions are there and what am I doing wrong here? Seems like an ISERROR type of approach or SEARCH but I'm stuck. TIA Tom 
|
|
#2
October 2nd, 2006, 05:14 PM
posted to microsoft.public.excel.setup
|
|||
|
|||
|
SEARCH function within IF function
I think you will need to do:
IF(ISNUMBER(Search("Customer",B2,1)),"Yes","No") because SEARCH will return #VALUE if the text is not found in B2. Hope this helps. Pete Tom wrote: Excel 2003 SP2 I'm attempting to use the SEARCH function for a couple of strings in a cell. Specifically, I need to search for "Customer", "Vendor" and "Item". Seems simple but when I use the following IF statement, I get a #VALUE response on the FALSE condition. Here's the statement IF(Search("Customer",B2,1)0,"Yes","No") Columns==== B C D E Customer Master File =If(Search...) =if(Search....) =If(Search...) Vendor Master File etc etc etc Item Master File etc etc etc If Bxxxx has "Customer" I get the "Yes". However, if Bxxxx does NOT have "Customer" I get the #Value response. I've tried several iterations of nested IFs and other SEARCHes but I'm still getting #VALUE. My desired result is if Bxxxx has "Customer" then "Yes", if Bxxxx has "Vendor" then "No" or blank. I'm essentially establishing columns C, D and E to be Customer, Vendor and Item Related. Hence, if Bxxxx contains "Customer" then Cxxxx would be "Yes". If Bxxxx contains "Vendor" then Dxxxx would be "Yes" and if Bxxxx contains "Item" then Exxxx would be "Yes". Otherwise, I'd like to see a "No" or a blank (""). What solutions are there and what am I doing wrong here? Seems like an ISERROR type of approach or SEARCH but I'm stuck. TIA Tom
|
|
#3
October 2nd, 2006, 05:22 PM
posted to microsoft.public.excel.setup
|
|||
|
|||
|
SEARCH function within IF function
A different approach would be
=IF(ISERROR(SEARCH("Customer",B2,1)),"No","Yes") -- kassie never stop learning "Tom" wrote in message ... Excel 2003 SP2 I'm attempting to use the SEARCH function for a couple of strings in a cell. Specifically, I need to search for "Customer", "Vendor" and "Item". Seems simple but when I use the following IF statement, I get a #VALUE response on the FALSE condition. Here's the statement IF(Search("Customer",B2,1)0,"Yes","No") Columns==== B C D E Customer Master File =If(Search...) =if(Search....) =If(Search...) Vendor Master File etc etc etc Item Master File etc etc etc If Bxxxx has "Customer" I get the "Yes". However, if Bxxxx does NOT have "Customer" I get the #Value response. I've tried several iterations of nested IFs and other SEARCHes but I'm still getting #VALUE. My desired result is if Bxxxx has "Customer" then "Yes", if Bxxxx has "Vendor" then "No" or blank. I'm essentially establishing columns C, D and E to be Customer, Vendor and Item Related. Hence, if Bxxxx contains "Customer" then Cxxxx would be "Yes". If Bxxxx contains "Vendor" then Dxxxx would be "Yes" and if Bxxxx contains "Item" then Exxxx would be "Yes". Otherwise, I'd like to see a "No" or a blank (""). What solutions are there and what am I doing wrong here? Seems like an ISERROR type of approach or SEARCH but I'm stuck. TIA Tom
|
|
#4
October 2nd, 2006, 06:03 PM
posted to microsoft.public.excel.setup
|
|||
|
|||
|
SEARCH function within IF function
Gads.... So close but SOOOO far. Thanks to both of you who assisted!
:-) "Kassie" wrote: A different approach would be =IF(ISERROR(SEARCH("Customer",B2,1)),"No","Yes") -- kassie never stop learning "Tom" wrote in message ... Excel 2003 SP2 I'm attempting to use the SEARCH function for a couple of strings in a cell. Specifically, I need to search for "Customer", "Vendor" and "Item". Seems simple but when I use the following IF statement, I get a #VALUE response on the FALSE condition. Here's the statement IF(Search("Customer",B2,1)0,"Yes","No") Columns==== B C D E Customer Master File =If(Search...) =if(Search....) =If(Search...) Vendor Master File etc etc etc Item Master File etc etc etc If Bxxxx has "Customer" I get the "Yes". However, if Bxxxx does NOT have "Customer" I get the #Value response. I've tried several iterations of nested IFs and other SEARCHes but I'm still getting #VALUE. My desired result is if Bxxxx has "Customer" then "Yes", if Bxxxx has "Vendor" then "No" or blank. I'm essentially establishing columns C, D and E to be Customer, Vendor and Item Related. Hence, if Bxxxx contains "Customer" then Cxxxx would be "Yes". If Bxxxx contains "Vendor" then Dxxxx would be "Yes" and if Bxxxx contains "Item" then Exxxx would be "Yes". Otherwise, I'd like to see a "No" or a blank (""). What solutions are there and what am I doing wrong here? Seems like an ISERROR type of approach or SEARCH but I'm stuck. TIA Tom
|
|
#5
October 2nd, 2006, 08:35 PM
posted to microsoft.public.excel.setup
|
|||
|
|||
|
SEARCH function within IF function
You're welcome, Tom.
Pete Tom wrote: Gads.... So close but SOOOO far. Thanks to both of you who assisted! :-) "Kassie" wrote: A different approach would be =IF(ISERROR(SEARCH("Customer",B2,1)),"No","Yes") -- kassie never stop learning "Tom" wrote in message ... Excel 2003 SP2 I'm attempting to use the SEARCH function for a couple of strings in a cell. Specifically, I need to search for "Customer", "Vendor" and "Item". Seems simple but when I use the following IF statement, I get a #VALUE response on the FALSE condition. Here's the statement IF(Search("Customer",B2,1)0,"Yes","No") Columns==== B C D E Customer Master File =If(Search...) =if(Search....) =If(Search...) Vendor Master File etc etc etc Item Master File etc etc etc If Bxxxx has "Customer" I get the "Yes". However, if Bxxxx does NOT have "Customer" I get the #Value response. I've tried several iterations of nested IFs and other SEARCHes but I'm still getting #VALUE. My desired result is if Bxxxx has "Customer" then "Yes", if Bxxxx has "Vendor" then "No" or blank. I'm essentially establishing columns C, D and E to be Customer, Vendor and Item Related. Hence, if Bxxxx contains "Customer" then Cxxxx would be "Yes". If Bxxxx contains "Vendor" then Dxxxx would be "Yes" and if Bxxxx contains "Item" then Exxxx would be "Yes". Otherwise, I'd like to see a "No" or a blank (""). What solutions are there and what am I doing wrong here? Seems like an ISERROR type of approach or SEARCH but I'm stuck. TIA Tom
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
