Variable Dependent Calculations

[SeveralTradesLater]

Hi Excel Community/Smart folks,

What formula would I used to resolve the challenge below? Where,

If Cell A2 is Product Category A then D2*C2 , if A2 is Product Category B then D2+E2*C2.​

Assume the product list has 20 or so more products.

Hope someone can assist.

Thank you in advance.

Sheet 1: Rates
​

​	A​	B​	C​	D​	​
1​	Product​	Fee Percentage 1​	Fee Percentage 2​	Product Category​	​
2​	Orange​	15%​	5%​	A​	​
3​	Apple​	18%​	3%​	B​	​
4​	Cherries​	33%​	3%​	B​	​
5​	Banana​	18%​	2%​	B​	​

Sheet 2: Product Order Form
​

​	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Product Type (Data Validation List Based on Rates Sheet)​	Fee Percentage 1​	Fee Percentage 2​	Product Value​	Product Value Based on Fee Percentage 1​	Variable Calculation Logic​	​	​	​
2​	Orange​	15%​	5%​	$3000​	=D2*B2​	If Cell A2 is Product Category A then D2*C2 , if A2 is Product Category B then D2+E2*C2.​	​	​	​
3​	Apple​	18%​	3%​	$2000​	=D3*B3​	"​	​	​	​
4​	Cherries​	33%​	3%​	$1000​	=D4*B4​	"​	​	​	​
5​	Banana​	18%​	2%​	$3000​	-D5*B5​	"​	​	​	​
​	​	​	​	​	​	​	​	​	​
​	​	​	​	​	​	​	​	​	​
​	​	​	​	​	​	​	​	​	​

​

 

[iggydarsa]

=IF(VLOOKUP(A2,Rates!A:D, 4, FALSE)="A", D2*C2, IF(VLOOKUP(A2,Rates!A:D, 4, FALSE)="B", D2+D2*C2*B2, 0))

 

[Bosquedeguate]

Try this ....

Define a name range from your first table. Let's call it RatesTable (Range:'Sheet1'!A2:E6)

For column E in Sheet 2 enter the following Function:
=VLOOKUP(A2,RateTable,2)*D4

GetLastRow.xlsm
	A	B	C	D
7	Product	Fee Percentage 1	Fee Percentage 2	Product Category
8	Orange	15%	5%	A
9	Apple	18%	3%	B
10	Cherries	33%	3%	B
11	Banana	18%	2%	B
Sheet5

You should be able to delete Columns B and C from Sheet2 since these percentages are defined in Sheet1

GetLastRow.xlsm
	A	B	C	D
22	Product Type (Data Validation List Based on Rates Sheet)	Product Value	Product Value Based on Fee Percentage 1	Variable Calculation Logic
23	Orange	$3,000	$450	$3,009
24	Apple	$2,000	$360	$2,011
25	Cherries	$1,000	$330	$1,010
26	Banana	$3,000	$540	$3,016
Sheet4

Cell Formulas
Range	Formula
C23:C26	C23	=VLOOKUP(A23,RateTable,2,FALSE)*B23
D23:D26	D23	=IF(VLOOKUP(A23,RateTable,4)="A",VLOOKUP(A23,RateTable,3)*B23,B23+VLOOKUP(A23,RateTable,3)*C23)

Named Ranges
Name	Refers To	Cells
RateTable	=Sheet5!$A$7:$D$11	C23:D26

Cells with Data Validation
Cell	Allow	Criteria
A23:A26	List	=Sheet5!$A$8:$A$11

 

[Peter_SSs]

I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.

Best I can understand is that this would be another option.

SeveralTradesLater.xlsm
	A	B	C	D
1	Product	Fee Percentage 1	Fee Percentage 2	Product Category
2	Orange	15%	5%	A
3	Apple	18%	3%	B
4	Cherries	33%	3%	B
5	Banana	18%	2%	B
Rates

SeveralTradesLater.xlsm
	A	B	C	D	E	F
1	Product Type	Fee Percentage 1	Fee Percentage 2	Product Value	Product Value Based on Fee Percentage 1	Variable Calc
2	Orange	15%	5%	$3,000	$450	150
3	Apple	18%	3%	$2,000	$360	2010.8
4	Cherries	33%	3%	$1,000	$330	1009.9
5	Banana	18%	2%	$3,000	$540	3010.8
Product Order Form

Cell Formulas
Range	Formula
E2:E5	E2	=D2*B2
F2:F5	F2	=CHOOSE(FIND(VLOOKUP(A2,Rates!A$2:D$5,4,0),"AB"),D2*C2,D2+E2*C2)