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?

 

[Fluff]

Is this what you mean

+Fluff 1.xlsm
	A	B	C
19	tow	A	A
20	tree	B
21	storage	C	C
22	tow storage	D	D
23	flower store	E
24	flower tow tree	F	F
Master

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

 

[HeRoseInThree]

=IF(OR(ISNUMBER(SEARCH("storage",A19)),ISNUMBER(SEARCH("tow",A19))),B19,"")
This works for the first search word, not the second...

I need to put a word into a19 (let's say tow)
then in b19, a dollar amount for the tow. 1000

What I'd like to happen is for Excel to know that this is a tow bill and that it cost $1,000 as opposed to storage charges for 500 which will be entered in another cell...

 

[Fluff]

Maybe

=IF(ISNUMBER(SEARCH("storage",A19)),500,IF(ISNUMBER(SEARCH("tow",A19)),1000,""))

If that doesn't work please post some sample data showing expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[HeRoseInThree]

N&I - BLANK - repair bill - ESTIMATE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
19	storage	4555			Storage fees							$4,555.00
20	inspect				Inspect to determine necessary repairs							$131.25
21	tow	555			Towing							$4,555.00
Sheet1

Cell Formulas
Range	Formula
E19:E21	E19	=IF(ISBLANK(A19),"",IF(A19="tow","Towing",IF(A19="etow","Emergency roadside towing",IF(A19="itow","In house towing",IF(A19="inspect","Inspect to determine necessary repairs",IF(A19="storage","Storage fees",IF(A19="scene","Clean up at scene",IF(A19="prop","Other real property damaged",IF(A19="wc","Subrogation for W/C injury",IF(A19="gas","Diesel fuel",IF(A19="parts","misc parts",IF(A19="ship","Shipping for parts",IF(A19="cool","Reclaim refrigerant, replace and perform leak check",IF(A19="fea","Front end alignment",IF(A19="h20","12 gallons of antifreeze @ $6.73",IF(A19="bodyp","Body shop (parts & materials)",IF(A19="bodyl","Body shop (labor)",IF(A19="misc","Misc shop procedures, supplies & administrative fees",IF(A19="af","Antifreeze", IF(A19="report","Purchase accident report",0))))))))))))))))))))
L19:L21	L19	=IF(ISBLANK(A19),"",IF(E19="Towing",R$9,IF(E19="In house towing",R$14,IF(E19="itow","In house towing",IF(E19="Storage fees",R$25,IF(ISNUMBER(SEARCH("diesel",E19)),R$34,IF(E19="Inspect to determine necessary repairs",R$29,IF(E19="Storage fees",R$24,IF(E19="Body shop (labor)",R$22,IF(E19="Body shop (parts & materials)",T$20,IF(E19="Front end alignment","$412.78",IF(E19="Antifreeze ",T$34,IF(E19="misc parts",O$11,IF(E19="Shipping for parts",T$28, IF(E19="Misc shop procedures, supplies & administrative fees",Sheet2!B$1,0)))))))))))))))

 

[Fluff]

So what's your question? You already seem to have something that works.

 

[HeRoseInThree]

I need to put a word into a19 (let's say tow)
then in b19, a dollar amount for the tow. 1000

What I'd like to happen is for Excel to know that this is a tow bill and that it cost $1,000 as opposed to storage charges for 500 which will be entered in another cell...

 

[HeRoseInThree]

N&I - BLANK - repair bill - ESTIMATE.xlsx
	Q	R	S	T	U
7
8		outside towing
9		$4,555.00
10
11		in house towing
12		6.5
13		$250.00
14		$1,625.00
15
16		Body shop - labor		Body shop - parts
17		5.21		$1,202.72
18				$0.73
19				$521.00
20		5.21		$1,724.45
21		$85.00
22		$442.85		steering fluid
23				4
24		storage fees		$4.34
25		$4,555.00		$17.36
26
27		inspection hours		shipping
28		1.25		$824.00
29		$131.25
30
31		fuel gallons		Antifreeze
32		110		15
33		$2.87		$6.73
34		$315.70		$100.95
35
Sheet1

Cell Formulas
Range	Formula
R9	R9	=IF(OR(ISNUMBER(SEARCH("storage",A19)),ISNUMBER(SEARCH("tow",A19))),B19,"")
R14,T34,R34,R22	R14	=R12*R13
R20	R20	=SUM(R17:R19)
T20	T20	=IF(ISBLANK(T17),"",SUM(T17:T19))
R25	R25	=IF(SUM(COUNTIF(A19,{"*storage*","*tow*"})),B$19,"")
T25	T25	=T24*T23
R29	R29	=R28*C45

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
T31:T33	Cell Value	=0	text	NO
R25:R33	Cell Value	=0	text	NO

 

[jtakw]

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.

Did you look at my sample in Post #7 ?

The COUNTIF statement mimics the SEARCH function in my formula, the result was B19 = cake, Not a count of the words "tow" or "storage", the COUNTIF found the keywords and output whatever is in B19, which can be your Dollar amount for the charge.
And it seems to do what you described, in your post # 17 cell R25

 

[Fluff]

What I'd like to happen is for Excel to know that this is a tow bill and that it cost $1,000 as opposed to storage charges for 500 which will be entered in another cell...

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

 

[jtakw]

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,"")