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?

 

[jtakw]

Perhaps you're looking for something like this, guessing here, your description is not clear:

Book3.xlsx
	A	B																R
12																		in house towing
13																		555
14
15
16
17																		Body shop - labor
18
19	storage	4555
20	inspect
21	tow	555
22
23
24
25																		storage fees
26																		4555
Sheet756

Cell Formulas
Range	Formula
R13,R26	R13	=LOOKUP(1,1/SEARCH($A$19:$A$21,R12),$B$19:$B$21)

 

[HeRoseInThree]

Exact same formula as in my Post # 7, and as I said in Post # 8, you can include as many Keywords inside the curly brackets as you want.

If it helps you see it more clearly, I've changed "cake" to $1,500.00

Book3.xlsx
	A	B	C
19	tow	$1,500.00	$1,500.00
20	tree
21	Storage		$1,500.00
22	tree Tow		$1,500.00
23	flower storage		$1,500.00
24	flower tow tree		$1,500.00
Sheet753

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

N&I - BLANK - repair bill - ESTIMATE.xlsx
	A	B	C
19	storage	4555	outside towing
20	inspect		$ 4,555.00
21	tow	555
22	itow		storage fees
23	gas		$4,555.00
Sheet1

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

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C23	Cell Value	=0	text	NO

 

[HeRoseInThree]

If it worked, I wouldn't keep asking for help.
The tow shouldn't be 4555, yet that is the result.

 

[HeRoseInThree]

Isn't that what the formula in post#13 does?

Nope. This is that result.

N&I - BLANK - repair bill - ESTIMATE.xlsx
	A	B	C
19	storage	4555	outside towing
20	inspect		$ 500.00
21	tow	555
22	itow		storage fees
23	gas		$500.00
Sheet1

Cell Formulas
Range	Formula
C20	C20	=IF(ISNUMBER(SEARCH("storage",A19)),500,IF(ISNUMBER(SEARCH("tow",A19)),1000,""))
C23	C23	=IF(ISNUMBER(SEARCH("storage",A19)),500,IF(ISNUMBER(SEARCH("tow",A19)),1000,""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C23	Cell Value	=0	text	NO

 

[Fluff]

Nope. This is that result.

That's because A19 is storage & not tow.

If it worked, I wouldn't keep asking for help.

Then maybe you need to explain better exactly what you are trying to do, as it stands we are all just guessing.
Please remember that while you know exactly what should happen, where & when. We do not.

 

[jtakw]

Look at my formula in Post # 21 above using your setup in your post # 14 & 17.

Here's my Post #21 formula using your setup in Your post # 22 & 24:

Book3.xlsx
	A	B	C
19	storage	4555	outside towing
20	inspect		555
21	tow	555
22	itow		storage fees
23	gas		4555
Sheet757

Cell Formulas
Range	Formula
C20,C23	C20	=LOOKUP(1,1/SEARCH($A$19:$A$23,C19),$B$19:$B$23)

 

[HeRoseInThree]

That's because A19 is storage & not tow.

Then maybe you need to explain better exactly what you are trying to do, as it stands we are all just guessing.
Please remember that while you know exactly what should happen, where & when. We do not.

I really am trying to explain it as well as I can and I REALLY do appreciate the help. I am a little frustrated that I can't get it to work.

 

[HeRoseInThree]

I really am trying to explain it as well as I can and I REALLY do appreciate the help. I am a little frustrated that I can't get it to work.

I need a19 to be any of the items (storage, tow or gas) and then Excel determine which dollar value goes with whatever is in a19.

Then, for a20, a21 and so on...

 

[Fluff]

Maybe

=VLOOKUP("tow",A19:B23,2,0)

 

[HeRoseInThree]

Maybe

=VLOOKUP("tow",A19:B23,2,0)

Ok, but what about having multiples? Looking for tow, storage and all the other words...