Problems nesting IF and SEARCH functions {SOLVED}

[salmonchild]

Afternoon all, currently having trouble nesting the above functions to return the result I want.


I have a column with job tiles in it, there are three job titles though a couple of instances for each e.g. Trainee Customer Advisor and Customer Advisor, Assistant Manager and Customer Service Manager, Trainee Apprentice and Customer Service Apprentice.


I want the return in the cell to be CSA, CSM or APP and have put together the following which only reloves for the first instance (Advisor) for the others it returns #VALUE!.


=IF(SEARCH("*Advisor*",B2),"CSA",IF(SEARCH("*Manager*",B2),"CSM",IF(SEARCH("*App*",B2),"APP","N/A")))


Does SEARCH return a true/false or just the text if it is there?

 

[Hui]

Search returns the position number of the text in the destination

If it isn't found it returns an #Value! error


So you'll need to restructure your formula

 

[SirJB7]

Hi, salmonchild!


SEARCH function retrieves the position where a string is found into another, i.e. a numeric integer value from 1 thru length of 2nd string, but if not found it retrieves the error value #¡VALUE!. So boolean values TRUE/FALSE are not available, hence your formula should look like this:


=SI(ESERROR(HALLAR("*Advisor*";B2));SI(ESERROR(HALLAR("*Manager*";B2));SI(ESERROR(HALLAR("*App*";B2));"N/A";"APP");"CSM");"CSA") -----> in english: =IF(ISERROR(SEARCH("*Advisor*",B2)),IF(ISERROR(SEARCH("*Manager*",B2)),IF(ISERROR(SEARCH("*App*",B2)),"N/A","APP"),"CSM"),"CSA")


Regards!


EDITED


@Hui

Hi!

Ooops... didn't read you... as usual... sorry for overstepping.

Regards!

 

[salmonchild]

When I separate it and just do the SEARCH it returns a value of 1, can I make my logical test SEARCH(("*Advisor*",B2)=1)?


Will try that now.


Cheers HUI

 

[SirJB7]

Hi, salmonchild!

Yes, you can do that, but it'll only works if the string is found and the function returns a numeric value. If it isn't found then you'll get a error value because of the comparison against 1.

Regards!

 

[salmonchild]

SIrJB7- thank you, now I see the logic and it works.


Cheers guys problem solved.

 

[SirJB7]

Hi, salmonchild!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Just as a tip, remember that if you enter in an empty cell "=SEARCH(" (unquoted) and press the "fx" button at the left of the edition bar text box, you'll be prompted with the function wizard which shows every argument, gives a brief description of the function and by the bottom left link lets you access the built-in or online help. It works for any function.

Regards!