I am trying to write an IF formula for an order form where a discount is applied depending on the amount of units ordered.

The discounts are:

  • 0 to 24 units - 0 discount
  • 24 to 59 units – 5% discount
  • 60 to 95 units – 10% discount
  • 96 to 131 units – 15% discount
  • 132 to 263 units – 20% discount
  • 264+ units - 30% discount

The cell that I am taking the total from is J1028. I have no idea how to do IF formulas so desperately need some help.

  • 1
    Why is there an umlaut over the H in this question? Oct 2 '12 at 2:08

Similar to @chrisneilsen's response, you could try using a VLOOKUP formula with the range_lookup parameter set to True:


This requires that the lookup table be sorted in ascending order by Units, with each Unit value representing the lower bound of the discount range:

enter image description here


For flexibility (so discounts and breakpoints can be changed later without necessarily needing to revise the formula) I’d suggest a table as in L1:M6 in the example below. Then apply the IF formula as shown, without the need for sorting or adding a limit value.


The IF construction is (test,pass,fail) and when nested the sequence is left to right.

[Assumes 5% discount for 24 units]

  • 1
    I think @chrisneilsen and I were both aiming more for flexibility - using INDEX/MATCH or VLOOKUP, if the OP needed to completely rework their bands and add/remove multiple criteria, they would only need to add the adjustments and resize the range in the formula. With an IF (namely a nested one), you would have to manually account for each change separately. Plus, pre-Excel 2007, the nested IFs limit is 7. Oct 19 '12 at 20:36

This tutorial will walk you through what you'll need to know about IF statements in Excel 2007.

Have a look at the Complex If Functions part; it should point you in the right direction.


Actually IF is not the best way to do this. Use Index Match instead.

Setup your discount table as shown below.
The reverse order of quant is needed for Match to work.
The large number 1E10 is an arbitary number, larger than any order you will receive.



  • 1
    @pnuts IF is generally better understood than : thats a matter of opinion. IMO Index Match (or VLookup for that matter) is basic Excel, and is easier to understand and debug than multiple nested IF's Oct 19 '12 at 21:49
  • 1
    @pnuts or 6 of one :) Oct 19 '12 at 23:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.