Need help with a formula I am trying to create

[zero20]

Hello, I am trying to create a formula that will automatically assign categories to our online products based on the values in another cell

Here's an example of what I've been trying, but it does not work because it only returns the first value that it successfully matches. I need it to return all matched values.

="default category"&IF(OR(ISNUMBER(SEARCH({"shirts"},A2))),",category 1",IF(OR(ISNUMBER(SEARCH({"blouses"},A2))),",category 2",IF(OR(ISNUMBER(SEARCH({"dresswear"},A2))),",category 3")))

("default category" is a fixed value that needs to always be at the beginning of this cell)

The result I get with this formula:

product type	categories
shirts|blouses|dresswear	default category,category 1

The result I am trying to get:

product type	categories
shirts|blouses|dresswear	default category,category 1,category 2,category 3

Any help or advice is greatly appreciated. Thank you

 

[offthelip]

try this:

="default category"&IF((ISNUMBER(SEARCH({"shirts"},A2))),",category 1", "")&IF((ISNUMBER(SEARCH({"blouses"},A2))),",category 2","")&IF(OR(ISNUMBER(SEARCH({"dresswear"},A2))),",category 3","")

 

[zero20]

try this:

="default category"&IF((ISNUMBER(SEARCH({"shirts"},A2))),",category 1", "")&IF((ISNUMBER(SEARCH({"blouses"},A2))),",category 2","")&IF(OR(ISNUMBER(SEARCH({"dresswear"},A2))),",category 3","")

This worked, thank you so much