SEARCH function within IF function

[Tom]

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

 

[Pete_UK]

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

 

[Kassie]

A different approach would be
=IF(ISERROR(SEARCH("Customer",B2,1)),"No","Yes")

 

[Tom]

Gads.... So close but SOOOO far. Thanks to both of you who assisted!

 

[Pete_UK]

You're welcome, Tom.

Pete

Gads.... So close but SOOOO far. Thanks to both of you who assisted!