Nesting if statements when a partial word match is found

**knakw00** · 01-22-2013, 04:59 PM

Good Day,

I a spreadsheet that i need to return multiple catagories if certain words are matched.

For example:

C2 may contain "Kwik Trip" or "the Store" or "Hal's Supermarket". I want column D2 to return the word "Gas" if A2 has "Kwik Trip" or "the Store" anywhere in it. I want D2 to return the word "Groceries" if column A has "Hals Supermarket" anywhere in it. C2 will contain only one of the qualifiers in it. C2 will however contain more than just those words IE: "KWIK TRIP GAS AND GO" or "THE STORE BREAD AND BUTTER STOP".

I have tried using the formula =if(c2="*Kwik*","Gas"),if(c2="*The Store*","Gas"),if(c2="*Hals*","Groceries") . This doesn't seem to recognize that I want a return if the partial words are met.

Thanks!!

**ChemistB** · 01-22-2013, 05:14 PM

Try this

=IF(OR(ISNUMBER(SEARCH("Kwik Trip",C2)),ISNUMBER(SEARCH("the store",C2))),"True", "False")
Does that work for you?

**knakw00** · 01-22-2013, 05:32 PM

Not that I can see. I want a specific word like "Gas" to be returned if C2 = "Kwik" or "Store" or to return the word "grocery" if C2 = "Hals"

**Tony Valko** · 01-22-2013, 05:40 PM

Create a 2 column table with the name in the left column and the category in the right column:

Please Login or Register  to view this content.

Then, enter this formula in D2:

=IFERROR(LOOKUP(1E100,SEARCH(H$2:H$4,C2),I$2:I$4),"")

**knakw00** · 01-23-2013, 10:25 AM

Thanks Biff, worked great. Not the way i was trying to do it but easier than nexting the formula over and over. Just like your tag says "Keep it simple stupid"