Calculation based on variables

[phitho]

I am having a time and a half with something that is probably super easy for anyone else, but here goes...

I have 3 cells:

Cell1 is a dropdown with 3 options (O1, O2, O3).
Cell2 is a Qty
Cell3 is where I would calculate the cost.

For Cell2, I have price breaks for different purchasing volumes: 1-100, 101-200, and 201-300

In a costing table, I have 3 lines, O1, O2, and O3. I then have 3 columns per line, with a discounted price for each Option. The column's pricing is based on volume: 1-100, 101-200, and 201-300.

With all these conditions, I'm banging my head trying to find how to calculate each price based on Option and volume.

Any help is greatly appreciated!

 

[StephenCrump]

Perhaps:

	A	B	C	D	E
1
2			1-100	101-200	>200
3			0	101	201
4		A	$2.00	$1.90	$1.80
5		B	$0.60	$0.55	$0.40
6		C	$1.50	$1.30	$1.10
7
8
9		B	201	$0.40
a

Cell Formulas
Range	Formula
D9	D9	=INDEX(Prices,MATCH(B9,Items,),MATCH(C9,Qty,1))

Named Ranges
Name	Refers To	Cells
Items	=a!$B$4:$B$6	D9
Prices	=a!$C$4:$E$6	D9
Qty	=a!$C$3:$E$3	D9

 

[Eric W]

Try:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H
1	Option	Qty	Price		Option	Qty1	Qty2	Qty3
2	O2	11	4		O1	1	2	3
3					O2	4	5	6
4					O3	7	8	9
Sheet9

Cell Formulas
Range	Formula
C2	C2	=VLOOKUP(A2,E2:H4,MATCH(B2,{0,101,201})+1)

 

[phitho]

Both answers return the right Qty from the range, but I need to calculate cost based on Qty. Using StephenCrump's post, if I have a Qty of 90 of Option B, it would be 90*$0.60. If I had 115, it would be 115*$0.55, and so on...

Thanks again, I've already learned how to use Match, which is great!

 

[Fluff]

With Stephen's layout, how about

=INDEX(Prices,MATCH(B9,Items,),MATCH(C9,Qty,1))*C9