# Multiple "IF" formulas

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.

• 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`:

``````=VLOOKUP(D2,\$A\$2:\$B\$7,2,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: 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]

• 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 `IF`s 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.

``````=INDEX(\$B\$2:\$B\$7,MATCH(J1028,\$A\$2:\$A\$7,-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
• @pnuts or 6 of one :) Oct 19 '12 at 23:16