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

#### bbbuffalo

##### New Member
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

##### Well-known Member
Perhaps =LOOKUPa1,{0,100,200,300,400,500),{0,0.05,0.07,0.1,0.15,0.25})

#### mse330

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

#### bbbuffalo

##### New Member
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

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

#### arthurbr

##### Well-known Member
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)

1,081,983
Messages
5,362,550
Members
400,679
Latest member
alecalec202

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...