If formula, with a twist

[HeRoseInThree]

I have an if formula that has a variable. I'd like to search for a keyword (diesel) and if it is found, return a string. I thought about IF(F23="*diesel",S$33 but that didn't work.

Any ideas?

 

[Joe4]

Try this:

=IF(ISNUMBER(SEARCH("diesel",F23)),S$33,"")

 

[HeRoseInThree]

Thank you!!!

The scary thing is, I'm the go to person in my office for all things Excel. You are making me look good!

 

[Joe4]

You are welcome!

The key with that formula is that the "SEARCH" function returns the character where that word starts in the string.
If it is found, it returns a numbers If it is not found, it returns an error.
So we can use the ISNUMBER function to determine if it is found or not.

 

[HeRoseInThree]

Follow-up question. How would I connect multiples of these together?

This isn't working:
=IF(ISNUMBER(SEARCH("storage",A19)),B$19,IF(ISNUMBER(SEARCH("tow",A19)),B$19))

 

[Michael M]

Maybe this way

=IF(OR(ISNUMBER(SEARCH("storage",A19)),ISNUMBER(SEARCH("tow",A19))),B19,"")

 

[jtakw]

Hi,

You can also try this:

Book3.xlsx
	A	B	C
19	tow	cake	cake
20	tree
21	storage		cake
22	tree tow		cake
23	flower storage		cake
24	flower tow tree		cake
Sheet753

Cell Formulas
Range	Formula
C19:C24	C19	=IF(SUM(COUNTIF(A19,{"*storage*","*tow*"})),B$19,"")

 

[jtakw]

Too late to edit.

Please note, you can add as many Keywords as you want inside the curly brackets to my formula in Post #7.

 

[HeRoseInThree]

Maybe this way

=IF(OR(ISNUMBER(SEARCH("storage",A19)),ISNUMBER(SEARCH("tow",A19))),B19,"")

This didn't work properly.

 

[HeRoseInThree]

Hi,

You can also try this:

Book3.xlsx
	A	B	C
19	tow	cake	cake
20	tree
21	storage		cake
22	tree tow		cake
23	flower storage		cake
24	flower tow tree		cake
Sheet753

Cell Formulas
Range	Formula
C19:C24	C19	=IF(SUM(COUNTIF(A19,{"*storage*","*tow*"})),B$19,"")

I am not trying to count the number of times the word occurs, just whatever is in the B column if the word is in the A column.