How to Search text and return multiple values

[j_wilkinson305]

Hi all,

I'm a newbie with Excel formulas and I am struggling with trying to search a product description and return multiple values depending on the content.

In the example in the screenshot below I have various watches and clocks which I want to assign to specific categories.

So if the description contains "womens" or "ladies" I want to return the value of "Ladies" in column B

Likewise if the description contains;

"mens" I would like it to return "Mens" in column B

"boys" would like it to return "Boys" in column B

"girls" would like it to return "Girls" in column B

I am able to use a combination of IF, ISNUMBER and SEARCH to return one value ie "Ladies" but I'm struggling to work out how to combine these functions to search for multiple text and return multiple values

Any help appreciated,

Thanks

[#2 · Accepted solution]

@j_wilkinson305 

=IF(OR(ISNUMBER(SEARCH("womens",A1)),ISNUMBER(SEARCH("ladies",A1))),"Ladies",IF(ISNUMBER(SEARCH("boys",A1)),"Boys",IF(ISNUMBER(SEARCH("girls",A1)),"Girls",IF(ISNUMBER(SEARCH("mens",A1)),"Mens",""))))

Is this what you are looking for?

[#3]

@OliverScheurich  Indeed it is thanks for the quick response! :)