=if with drop down list using VLOOKUP

[RedleoUK]

Hi everyone,

Really hope someone can help me with this as I've been digging around the internet and racking my brains for days now.

I'm a sales person in a team and I'm looking at making a spreadsheet to help other people and myself create deals to sell to customers.

This is what I have made so far but I've hit a bit of a brick wall.

The person using this guide will first have to select if the customer is a B2B client from the drop down in B12. If they are a B2B customer then they get a 15% discount. If they are not they get 10%. The value in G10 will change dependent on the Yes or No selection.

However, they only get this discount if the value in B10 is 10 units or higher.

Is there a way to make the value in H10 switch between three possible outcomes?

So, if less then 10 it stays at £19.55.
If more than 10 it takes the percentage from G10 and puts the price in H10 either £16.62 for 15% or £17.60 if 10%.

Am I asking excel to do too much here? is this formula even possible??

Any help would be much appreciated.

 

[RedleoUK]

Here's a copy of my sheet if it will help someone

deal calculator.xlsx
	A
9
Deal Maker

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(B10<10,C10,C10*(1-G10))

 

[RedleoUK]

deal calculator.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1		Within the deal?	Wholesale price	Wholesale price +VAT	RRP Value (per Outer)	POR	Discount	Wholesale price	Wholesale price +VAT	RRP Value (per Outer)	POR
2			£ 50.00	£ 60.00	£ 320.00	£ 196.00		£ 50.00	£ 60.00	£ 320.00	£ 196.00
3			£ 32.50	£ 39.00	£ 59.90	£ 8.92	£ 7.50	£ 25.00	£ 30.00	£ 59.90	£ 17.92
4			£ 32.50	£ 39.00	£ 59.90	£ 8.92	£ 7.50	£ 25.00	£ 30.00	£ 59.90	£ 17.92
5			£ 32.50	£ 39.00	£ 59.90	£ 8.92	£ 7.50	£ 25.00	£ 30.00	£ 59.90	£ 17.92
6			£ 32.50	£ 39.00	£ 59.90	£ 8.92	£ 7.50	£ 25.00	£ 30.00	£ 59.90	£ 17.92
7			£ 32.50	£ 39.00	£ 59.90	£ 8.92	£ 7.50	£ 25.00	£ 30.00	£ 59.90	£ 17.92
8			£ 32.50	£ 39.00	£ 59.90	£ 8.92	£ 7.50	£ 25.00	£ 30.00	£ 59.90	£ 17.92
9			£ 13.50	£ 16.20	£ 24.95	£ 3.76		£ 13.50	£ 16.20	£ 24.95	£ 3.76
10			£ 19.55	£ 23.46	£ 34.95	£ 4.50	15%	£ 19.55	£ 23.46	£ 34.95	£ 4.50
11			Full Price	£ -	£ -	£ -		Discounted Price	£ -	£ -	£ -
12	B2B?	Yes
13
14
15
16
17
18
19
20
21	Answer	0
22	Yes	15%	£ 16.62
23	No	10%	£ 17.60
24
25		10
26
Deal Maker

Cell Formulas
Range	Formula
H3:H8	H3	=SUM(32.5-G3)
K2:K10,F2:F10	F2	=SUM((E2*0.8)-D2)
D2:D10,I2:I10	D2	=SUM((C2*0.2)+C2)
G10	G10	=VLOOKUP(B12,A21:B23,2,FALSE)
D11	D11	=SUM((B2*D2)+(B3*D3)+(B4*D4)+(B5*D4)+(B6*D5)+(B6*D6)+(B7*D7)+(B8*D8)+(B9*D9)+(B10*D10))
E11	E11	=SUM((B2*E2)+(B3*E3)+(B4*E4)+(B5*E4)+(B6*E5)+(B6*E6)+(B7*E7)+(B8*E8)+(B9*E9)+(B10*E10))
F11	F11	=SUM((B2*F2)+(B3*F3)+(B4*F4)+(B5*F4)+(B6*F5)+(B6*F6)+(B7*F7)+(B8*F8)+(B9*F9)+(B10*F10))
I11	I11	=SUM((B2*I2)+(B3*I3)+(B4*I4)+(B5*I4)+(B6*I5)+(B6*I6)+(B7*I7)+(B8*I8)+(B9*I9)+(B10*I10))
J11	J11	=SUM((B2*J2)+(B3*J3)+(B4*J4)+(B5*J4)+(B6*J5)+(B6*J6)+(B7*J7)+(B8*J8)+(B9*J9)+(B10*J10))
K11	K11	=SUM((B2*K2)+(B3*K3)+(B4*K4)+(B5*K4)+(B6*K5)+(B6*K6)+(B7*K7)+(B8*K8)+(B9*K9)+(B10*K10))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B12	Expression	=$B12="No"	text	NO
B12	Expression	=$B12="No"	text	NO
B12	Expression	=$B12="Yes"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B12	List	Answer,Yes,No

 

[Fluff]

Did you see my reply?