Formula that looks for a word in a cell and multiplies a value

[PETRODATAjames]

I would like a formula that, sorry I will not write this correctly, but here goes. I need this formula to look for a specific word in column, B Product Types and if the word is Credits then multiply by .045, if the word is Food and Beverage then multiply by .050, if the word is Penalty Fees then multiply by .085, and so on. I also would need it, if the word is water, then blank, no multiple by, or multiply by 0, and not display an error, like #REF or something.

 

[awoohaw]

what value are you multiplying the lookup value by?

 

[awoohaw]

Use this as one of the factors in your formula:

Mr excel questions 41.xlsm
	A	B	C	D	E	F	G
1			Using Lookup	Using Switch
2		Credits	0.045	0.45		Credits	0.045
3		Equipment	0.055	0.055		Equipment	0.055
4		Equipment	0.055	0.055		Food & Beverage	0.05
5		Food & Beverage	0.05	0.5		Memberships	0.065
6		Food & Beverage	0.05	0.5		Penalty Fees	0.085
7		Food & Beverage	0.05	0.5		Water	0
8		Memberships	0.065	0.065
9		Memberships	0.065	0.065
10		Memberships	0.065	0.065
11		Memberships	0.065	0.065
12		Penalty Fees	0.085	0.085
13		Water	0	0
PETRODataJames

Cell Formulas
Range	Formula
C2:C13	C2	=XLOOKUP(B2,$F$2:$F$7,$G$2:$G$7)
D2:D13	D2	=SWITCH(B2,"Credits",0.45,"Equipment",0.055,"Food & Beverage",0.5,"Memberships",0.065,"Penalty Fees",0.085, "Water",0)

 

[PETRODATAjames]

report-sales-tax-by-month-detail (3).csv
	A	B	C	D	E	F	G	H	I	J	K
1	Product	Product Type	Fulfillment Location	Purchase Location	Month	Tax Record	Revenue	Quantity	Currency	Tax Collected
2	5 Class Pack	Credits	NYC	In-Store	2023-06	NYC Tax - Credits	$ 150.00	$ 1.00	USD	$ 6.75	$ 6.75
3	54D Ab Mats	Equipment	NYC	In-Store	2023-06	NYC Tax - Equipment NYC	$ 39.99	$ 1.00	USD	$ 1.80	$ 1.80
4	54D Ab Mats	Equipment	NYC	In-Store	2023-06	NYC Tax - Equipment NY State	$ 39.99	$ 1.00	USD	$ 1.60	$ 1.80
5	C4 Energy	Food and Beverage	NYC	In-Store	2023-06	NYC Tax - Food and Beverages	$ 5.00	$ 1.00	USD	$ 0.23	$ 0.23
6	Celsius	Food and Beverage	NYC	In-Store	2023-06	NYC Tax - Food and Beverages	$ 18.00	$ 4.00	USD	$ 0.81	$ 0.81
7	Iconic Protein	Food and Beverage	NYC	In-Store	2023-06	NYC Tax - Food and Beverages	$ 25.00	$ 5.00	USD	$ 1.15	$ 1.13
8	2-Week Bootcamp	Memberships	NYC	In-Store	2023-06	NYC Tax - Memberships	$ 1,764.50	$ 4.00	USD	$ 79.41	$ 79.40
9	4-Week Bootcamp	Memberships	NYC	In-Store	2023-06	NYC Tax - Memberships	$ 2,700.00	$ 2.00	USD	$ 121.50	$ 121.50
10	54D Yearly Membership	Memberships	NYC	In-Store	2023-06	NYC Tax - Memberships	$ 3,324.60	$ 5.00	USD	$ 149.60	$ 149.61
11	54D Yearly Membership (One time payment)	Memberships	NYC	In-Store	2023-06	NYC Tax - Memberships	$ 2,250.00	$ 1.00	USD	$ 101.25	$ 101.25
12	No Show Fee	Penalty Fees	NYC	E-Commerce	2023-06	NYC Tax - Penalty Fees	$ 50.00	$ 1.00	USD
13	Water Bottle	Water	NYC	In-Store	2023-06	NYC Tax - Water NY State	$ 16.25	$ 5.00	USD	$ 0.65	$ 0.65
report-sales-tax-by-month-detai

Cell Formulas
Range	Formula
K2:K12	K2	=IF(ISBLANK(J2),"",G2*".045")
K13	K13	=IF(ISBLANK(J13),"",G13*".040")

 

[PETRODATAjames]

May I ask, if I would have to insert the columns with a value using SWITCH? This is the formula I have in place already: =IF(ISBLANK(J2),"",G2*".045"), but I have to edit the .045 value if required.

 

[awoohaw]

I gave you two options, one using XLOOKUP that you can easily update your factors as needed. The SWITCH function would require editing the function in the first cell and copying down. IF and SWITCH are not the same. They are similar but not the same. why are you puting the G2* inside the IF statement.
G2 * IF/SWITCH/XLOOKUP is more efficient syntax. But the IF is a less effective syntax.

To use XLOOKUP you would need the 2 helper columns (to hold the lookup table) TO use SWITCH no helper columns are needed.

 

[awoohaw]

=G2 * XLOOKUP(B2,$F$2:$F$7,$G$2:$G$7)

or

=G2 * SWITCH(B2,"Credits",0.45,"Equipment",0.055,"Food & Beverage",0.5,"Memberships",0.065,"Penalty Fees",0.085, "Water",0)

 

[PETRODATAjames]

You are a LEGEND!
The SWITCH formula is exactly what I need.

Thanks

 

[awoohaw]

I'm happy you found a solution, and thanks for the feedback.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Formula that is like a vlookup, just looking for a name in a cell and multiply by a value - OzGrid Free Excel/VBA Help Forum
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.