Cells contain specific text return value

Copper Contributor

Hi all

I need help please

The below works well in finding the word T-Shirts in a sentence in a column BUT I want to look for more than just that, I need to include anything that contains Shorts in the other rows and return the word Shorts, how do I add that in?

Formula:

=IF(ISNUMBER(SEARCH("T-Shirts",G17)),"Shirts")

 

Tried but not working:

=IF(ISNUMBER(SEARCH("T-Shirts",G4)),"Shorts"),IF(ISNUMBER(SEARCH("T-Shirts",G4)),"Shorts")

 

My data in column G looks like this:

000000-Mens Apparel Branded Shorts
000000-Mens Apparel Branded T-Shirts
000000x-Ladies Apparel Branded Denim

I just want to return if it contains Shorts, than Shorts, if it contains Denim, then Denim

Hope that I am making sense

Thank you 

 

2 Replies

@Rene81 

Enter all the words you want to look for in a range, one per cell.

HansVogelaar_0-1685524823765.png

Select the range.

Click in the name box on the left hand side of the formula bar.

Type a name, for example Types, and press Enter.

 

Let's say your data begin in G2.

Enter the following formula in H2, then fill down:

 

=IFERROR(INDEX(Types, MATCH(TRUE, ISNUMBER(SEARCH(Types, G2)), 0)), "")

@HansVogelaar Thank you so much that works that has truly been a great help:)