How to return a discount amount based on multiple ranges in Excel

[bbbuffalo]

I've got a table with the following columns

Quantity, Price, Sub Total, Discount Tier, Discount Amount, Total

If the quantity is between 100 and 200 I want to show a 5% discount in the Discount Tier column

If the quantity is between 201 and 300 I want to show a 7% discount in the Discount Tier column

If the quantity is between 301 and 400 I want to show a 10% discount in the Discount Tier column

If the quantity is between 401 and 500 I want to show a 15% discount in the Discount Tier column

If the quantity is greater than 500 I want to show a 25% discount in the Discount Tier column

I feel like it's a vlookup, but I need for it check multiple conditions as you can see. What is my formula for checking what is in the Quantity field against those discount ranges and returning the correct value to the Discount Tier field?

 

[arthurbr]

Perhaps =LOOKUPa1,{0,100,200,300,400,500),{0,0.05,0.07,0.1,0.15,0.25})

 

[mse330]

You can do that with nested IF condition formula as well as shown below

Book1
	A	B	C	D	E	F
1	Quantity	Price	Sub Total	Discount Tier	Discount Amount	Total
2	20			0%
3	120			5%
4	220			7%
5	320			10%
6	420			15%
7	520			25%
Sheet1

Cell Formulas
Range	Formula
D2	=IF(A2>=500,25%,IF(A2>400,15%,IF(A2>300,10%,IF(A2>200,7%,IF(A2>=100,5%,0)))))

 

[bbbuffalo]

Thank you for those solutions!

Is there a way to do it more like a Vlookup where I have another table (see below) that contains the minimum and maximum values along with the discount percentage so that it does something like this:

If Quantity is greater than Tier 1 Minimum but less than Tier 1 Maximum, then the Discount will be the Tier 1 Discount

And continue to check against all tiers

Tier Min Max Discount
1 100 200 0.05
2 201 300 0.07
3 301 400 0.1
4 401 500 0.15
5 501 0.25

 

[mse330]

You can use the formula suggested by arthurbr like below (adjust according to your data)

Book1
	A	B	C	D	E	F	G	H	I
1	Quantity	Price	Sub Total	Discount Tier	Discount Amount	Total		Min Amt	Discount
2	20				0%			0	0
3	120				5%			100	5%
4	220				7%			201	7%
5	320				10%			301	10%
6	420				15%			401	15%
7	520				25%			500	25%
Sheet1

Cell Formulas
Range	Formula
E2	=LOOKUP(A2,$H$2:$H$7,$I$2:$I$7)
E3	=LOOKUP(A3,$H$2:$H$7,$I$2:$I$7)
E4	=LOOKUP(A4,$H$2:$H$7,$I$2:$I$7)
E5	=LOOKUP(A5,$H$2:$H$7,$I$2:$I$7)
E6	=LOOKUP(A6,$H$2:$H$7,$I$2:$I$7)
E7	=LOOKUP(A7,$H$2:$H$7,$I$2:$I$7)

 

[arthurbr]

Perhaps =LOOKUPa1,{0,100,200,300,400,500),{0,0.05,0.07,0.1,0.15,0.25})

mixed it up a bit =LOOKUP(a1,{0,100,200,300,400,500},{0,0.05,0.07,0.1,0.15,0.25})
The numbers between brackets can be replaced by any range to like ( A1:A5 or whatever)