Multiple If/Isnumber/search formula

[Chewyhairball]

Hi

I have the following formula in cell S7 which if the information in cell R7 contains the word "liquid" it populates the word liquid to S7

e.g cell R7 might contain "liquid, hazardous, waste" in which case S7 will show "liquid".

IF(ISNUMBER(SEARCH("liquid",R7)),"Liquid","")

I am trying to figure out how to do this so it also looks for "Solid" and "gaseous" and populates the found word into S7

There will only ever be one of these words in R7 so it will either be "liquid, hazardous, waste" or "solid, hazardous, waste" or "gaseous, hazardous, waste"

I need it to find out which of these words it contains and put it into cell R7

thanks in advance

Rory

 

[Fluff]

Do you just want to return the 1st word from R7 to S7?

 

[AlanY]

try this

Book1
	A	B	C	D
1	liquid, hazardous, waste	Liquid		Liquid
2	gaseous, waste	Gaseous		Solid
3	waste			Gaseous
4	solid, waste	Solid
98

Cell Formulas
Range	Formula
B1:B4	B1	=IFERROR(LOOKUP(2,1/ISNUMBER(SEARCH($D$1:$D$3,A1)),$D$1:$D$3),"")

 

[Chewyhairball]

Do you just want to return the 1st word from R7 to S7?

Most of the time solid, liquid or gaseous would be the first word in the list but if it isnt then it would be good to still find it.

thanks

Rory

 

[Fluff]

Then try Alan's suggestion.

 

[Chewyhairball]

Thanks Guys

Alans suggestion works perfect for me.

thanks for both your replies

Rory

 

[AlanY]

Thanks Guys

Alans suggestion works perfect for me.

thanks for both your replies

Rory

You’re welcome

 

[Fluff]

@AlanY
I see your using the latest version of XL2BB, so you might want to update your signature.

 

[AlanY]

@AlanY
I see your using the latest version of XL2BB, so you might want to update your signature.

Cheers. Will do