IF AND CONTAINS formula

[jackthompson]

Hi,

I have a pivot table in excel.

I want to be able to make a new column at the end with the result of this.

I just cant figure the formula out.

for example

I need it to look at Cell J2 and if J2 contains "Premium" then Say Premium AND if it contains "Advanced" then say Advanced.

Possible?

 

[Fishboy]

Hi,

I have a pivot table in excel.

I want to be able to make a new column at the end with the result of this.

I just cant figure the formula out.

for example

I need it to look at Cell J2 and if J2 contains "Premium" then Say Premium AND if it contains "Advanced" then say Advanced.

Possible?

Hi JackThompson, welcome to the boards.

I assume despite your original post J2 will not have Premium AND Advanced, but rather Premium OR Advanced. If that is correct try this formula:

=IF(OR(ISNUMBER(SEARCH("premium",J2))),"Premium",IF(OR(ISNUMBER(SEARCH("advanced",J2))),"Advanced",""))

 

[jackthompson]

Hi JackThompson, welcome to the boards.

I assume despite your original post J2 will not have Premium AND Advanced, but rather Premium OR Advanced. If that is correct try this formula:

=IF(OR(ISNUMBER(SEARCH("premium",J2))),"Premium",IF(OR(ISNUMBER(SEARCH("advanced",J2))),"Advanced",""))

Absolutely perfect!!!

 

[Fishboy]

Absolutely perfect!!!

You're welcome. Feel free to hit the Like button to show support!

 

[Jonmo1]

If you're going to have more than just a couple key words to look for, try putting a list of those keywords in a range of cells.
Say N2:N3 (N2 = Premium, N3 = Advanced)

Then use
=LOOKUP(2^15,SEARCH($N$2:$N$3,J2),$N$2:$N$3)


There cannot be any blanks in N2:N3

 

[jackthompson]

actually... how , if possible, do you add an ELSE function so that everything other than those 2 criteria, will equal lets say "Other"

 

[Fishboy]

actually... how , if possible, do you add an ELSE function so that everything other than those 2 criteria, will equal lets say "Other"

Using my original example, simply change the "" at the end of the formula to "Other"