IF with many conditions

[sburkhar]

I need to write a formula that states:

If the cell is empty then nothing
if it contains "terminal" then "Term"
if it contains "wire"" then "wire"
if it contains anything else then "other".

but the cell will contain something like this:

70031171 Terminal;BC;Press/Lug/Tab/Pipe

so the formula will need to select the word "Terminal" out of the string of words to return "term" "wire" or "other"
Excel 2003

 

[MrKowz]

Try:

=IF(A1="","",IF(ISNUMBER(SEARCH("Terminal",A1)),"Term",IF(ISNUMBER(SEARCH("Wire",A1)),"Wire","Other"))

 

[mikerickson]

Perhaps
=IF(COUNTIF(A1,"*terminal*"), "Term", IF(COUNTIF(A1,"*wire*"), "wire", "other"))

 

[sburkhar]

Sweeeet! Thanks!

 

[sburkhar]

crap! I spoke too soon.
I should mention that the reference cell has a formula in it, so even though it LOOKS empty, it's returning "other".

 

[texasalynn]

crap! I spoke too soon.
I should mention that the reference cell has a formula in it, so even though it LOOKS empty, it's returning "other".

so is the formula returning a space perhaps? " " in the formula? or is it "" in the formula

 

[sburkhar]

This is what's in the reference cell:
='CHANGE MATRIX '!E26

It's pulling a concatenated formula from another page.

 

[sburkhar]

I think I can do this another way. I've created a VLOOKUP for the "Term" and "Other" results.
However, If the cell is populated and the VLOOKUP does not return "Term" or "Other" I need it to return "Wire".
Is this possible?

Or if it detects 180 or 181 at the beginning of the Part Number it should return "Wire"

 

[Dryver14]

MrKowz,

I ran the formula through the evalator and see that having Terminal in A1 generated a 1 so returned Term....why does it return a 1?

 

[sburkhar]

I'm sorry, I don't know what that means.