SOLVED

How to Search text and return multiple values

New Contributor

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

 

j_wilkinson305_0-1641499168421.png

Any help appreciated,

 

Thanks

 

2 Replies
best response confirmed by Hans Vogelaar (MVP)
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?

@Quadruple_Pawn  Indeed it is thanks for the quick response!