Creating Function to Reference Cells that Contain Certain Values
 

I want to insert a function, I assume an "IF" function that gives me back
different values depending on if the referenced cells contain certain
information.

For example:

A2: FS2-670-SB

If A2 contains the letters "SB" then the function with return "SMBS"

I want to do multiple IF functions so that depending on the "key" letters,
the function will return a certain answer

So, If the cell contains "SB" then return "SMBS", if cell contains "SMB"
cell returns "SMBS", if cell contains "SS" then return "SHIN", if cell
contains "BB" then cell returns "BBUL" etc.

Creating Function to Reference Cells that Contain Certain Values
 

One way...

Create a 2 column table like this:

SB...SMBS
SMB...SMBS
SS...SHIN
BB...BBUL

Assume that table is in the range G2:H5

A2 = FS2-670-SB

Enter this array formula** in B2:

=IF(COUNT(SEARCH(G$2:G$5,A2)),LOOKUP(1E100,SEARCH( G$2:G$5,A2),H$2:H$5),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Seraslynz" wrote in message
...
I want to insert a function, I assume an "IF" function that gives me back
different values depending on if the referenced cells contain certain
information.

For example:

A2: FS2-670-SB

If A2 contains the letters "SB" then the function with return "SMBS"

I want to do multiple IF functions so that depending on the "key" letters,
the function will return a certain answer

So, If the cell contains "SB" then return "SMBS", if cell contains "SMB"
cell returns "SMBS", if cell contains "SS" then return "SHIN", if cell
contains "BB" then cell returns "BBUL" etc.

Creating Function to Reference Cells that Contain Certain Values
 

Try the below (Case sensitive..)

=IF(ISERROR(FIND("SB",A2)),IF(ISERROR(FIND("SMB",A 2)),IF(ISERROR(FIND("SS",A2)),IF(ISERROR(FIND("BB" ,A2)),"","BBUL"),"SHIN"),"SMBS"),"SMBS")

If this post helps click Yes
---------------
Jacob Skaria


"Seraslynz" wrote:

I want to insert a function, I assume an "IF" function that gives me back
different values depending on if the referenced cells contain certain
information.

For example:

A2: FS2-670-SB

If A2 contains the letters "SB" then the function with return "SMBS"

I want to do multiple IF functions so that depending on the "key" letters,
the function will return a certain answer

So, If the cell contains "SB" then return "SMBS", if cell contains "SMB"
cell returns "SMBS", if cell contains "SS" then return "SHIN", if cell
contains "BB" then cell returns "BBUL" etc.

Creating Function to Reference Cells that Contain Certain Valu
 

One more question to add on, because that function did work:

If I have a function that contains "SB MS" or "SB MSD" I need it to return
blank (a quality control test... how would I work that into the function?

Thanks!



"Jacob Skaria" wrote:

Try the below (Case sensitive..)

=IF(ISERROR(FIND("SB",A2)),IF(ISERROR(FIND("SMB",A 2)),IF(ISERROR(FIND("SS",A2)),IF(ISERROR(FIND("BB" ,A2)),"","BBUL"),"SHIN"),"SMBS"),"SMBS")

If this post helps click Yes
---------------
Jacob Skaria


"Seraslynz" wrote:

I want to insert a function, I assume an "IF" function that gives me back
different values depending on if the referenced cells contain certain
information.

For example:

A2: FS2-670-SB

If A2 contains the letters "SB" then the function with return "SMBS"

I want to do multiple IF functions so that depending on the "key" letters,
the function will return a certain answer

So, If the cell contains "SB" then return "SMBS", if cell contains "SMB"
cell returns "SMBS", if cell contains "SS" then return "SHIN", if cell
contains "BB" then cell returns "BBUL" etc.

Creating Function to Reference Cells that Contain Certain Valu
 

=IF(ISERROR(FIND("SB MS",A2)),IF(ISERROR(FIND("SB",A2)),
IF(ISERROR(FIND("SMB",A2)),IF(ISERROR(FIND("SS",A2 )),
IF(ISERROR(FIND("BB",A2)),"","BBUL"),"SHIN"),"SMBS "),"SMBS"),"")

If this post helps click Yes
---------------
Jacob Skaria


"Seraslynz" wrote:

One more question to add on, because that function did work:

If I have a function that contains "SB MS" or "SB MSD" I need it to return
blank (a quality control test... how would I work that into the function?

Thanks!



"Jacob Skaria" wrote:

Try the below (Case sensitive..)

=IF(ISERROR(FIND("SB",A2)),IF(ISERROR(FIND("SMB",A 2)),IF(ISERROR(FIND("SS",A2)),IF(ISERROR(FIND("BB" ,A2)),"","BBUL"),"SHIN"),"SMBS"),"SMBS")

If this post helps click Yes
---------------
Jacob Skaria


"Seraslynz" wrote:

I want to insert a function, I assume an "IF" function that gives me back
different values depending on if the referenced cells contain certain
information.

For example:

A2: FS2-670-SB

If A2 contains the letters "SB" then the function with return "SMBS"

I want to do multiple IF functions so that depending on the "key" letters,
the function will return a certain answer

So, If the cell contains "SB" then return "SMBS", if cell contains "SMB"
cell returns "SMBS", if cell contains "SS" then return "SHIN", if cell
contains "BB" then cell returns "BBUL" etc.

Creating Function to Reference Cells that Contain Certain Valu
 

Thanks for the help, but now with this:

=IF(ISERROR(FIND("BB MSD",H3264)),IF(ISERROR(FIND("BB
MS",H3264)),IF(ISERROR(FIND("SB MSD",H3264)),IF(ISERROR(FIND("SMB
MSD",H3264)),IF(ISERROR(FIND("SB",H3264)),IF(ISERR OR(FIND("SMB",H3264)),IF(ISERROR(FIND("SS",H3264)) ,IF(ISERROR(FIND("BB",H3264)),"","BBUL"),"SHIN")," SMBS"),"SMBS"),""),""),""),"")

It says "The specified formula cannot be entered because it uses more levels
of nesting than are allowed in the current file format" -- any ideas on how I
can change my file format to accomodate?

"Jacob Skaria" wrote:

=IF(ISERROR(FIND("SB MS",A2)),IF(ISERROR(FIND("SB",A2)),
IF(ISERROR(FIND("SMB",A2)),IF(ISERROR(FIND("SS",A2 )),
IF(ISERROR(FIND("BB",A2)),"","BBUL"),"SHIN"),"SMBS "),"SMBS"),"")

If this post helps click Yes
---------------
Jacob Skaria


"Seraslynz" wrote:

One more question to add on, because that function did work:

If I have a function that contains "SB MS" or "SB MSD" I need it to return
blank (a quality control test... how would I work that into the function?

Thanks!



"Jacob Skaria" wrote:

Try the below (Case sensitive..)

=IF(ISERROR(FIND("SB",A2)),IF(ISERROR(FIND("SMB",A 2)),IF(ISERROR(FIND("SS",A2)),IF(ISERROR(FIND("BB" ,A2)),"","BBUL"),"SHIN"),"SMBS"),"SMBS")

If this post helps click Yes
---------------
Jacob Skaria


"Seraslynz" wrote:

I want to insert a function, I assume an "IF" function that gives me back
different values depending on if the referenced cells contain certain
information.

For example:

A2: FS2-670-SB

If A2 contains the letters "SB" then the function with return "SMBS"

I want to do multiple IF functions so that depending on the "key" letters,
the function will return a certain answer

So, If the cell contains "SB" then return "SMBS", if cell contains "SMB"
cell returns "SMBS", if cell contains "SS" then return "SHIN", if cell
contains "BB" then cell returns "BBUL" etc.