# 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? – Anderson Green Oct 2 '12 at 2:08
@AndersonGreen I've suggested it be edited to plain `I` since it seems the umlaut may be affecting the ability to find 'IF' when searching. – pnuts Dec 21 '12 at 18:23

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:

-
Neat, but perhaps a little risky in inexpert hands, at least without mention that `TRUE` here means "an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned". – pnuts Oct 19 '12 at 20:25

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. – RocketDonkey 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))
``````

-
Small point (row 1000+ is not helpful in an example!) but your formulae are inconsistent for what is to be matched. `IF` is generally better understood than `INDEX` and `MATCH` (and is mentioned in OP) so I'd be interested in why not the best way - performance? – pnuts Oct 19 '12 at 20:18
@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 – chris neilsen Oct 19 '12 at 21:49
Thank you (and @RocketDonkey) for the clarification. I tried googling “EXCEL ={function} function” for each of IF, LOOKUP, MATCH and INDEX and came up with about the same number of hits for IF as for the other three combined. Might indeed indicate IF is more exotic?! I fully agree complicated IF statements are difficult and take your point that adjusting the range would be a bit easier, though (in my opinion!) it is roughly “7 of one or half a dozen of the other” where the nests are simple and so similar. My excuse is the question title. – pnuts Oct 19 '12 at 22:31
@pnuts or 6 of one :) – RocketDonkey Oct 19 '12 at 23:16