People frequently use nested IF functions in Microsoft Excel to solve tiered commission calculations. In these situations, sales up to a certain amount earn a base commission. Once the revenue exceeds a certain amount, the commission percentage changes. The vice president of sales will often invent several different tiers for the commission plan, requiring multiple IF statements to be nested together as shown in the following figure:
It is interesting to note that the formula shown above would not have been possible in Excel 2003 and earlier. In those versions, you were not allowed to nest more than seven functions in a single formula. That limit was relaxed starting in Excel 2007, and you can now nest up to 32 functions in a single formula.
But just because you can nest more than seven functions does not mean you should. At some point, it makes sense to replace the entire set of nested IF statements with a special version of the VLOOKUP function.
To start, build a table that shows the commission rate for each level. Arrange the levels from the smallest sales amount to the largest, as shown in this figure:
This next step is optional, but your formula will be simpler if you create a name for the lookup table.
To do this, select the numbers in your table (excluding the headings). The Name Box is to the left of the formula bar. Click in the Name Box and type a one-word name, such as "Rates." When you press Enter, Excel will refer to this range as Rates. It is far easier to type Rates in a formula instead of 'Lookup Tables'!A2:B14.
Most VLOOKUP functions use a fourth argument of False. This argument requires an exact match to be found in the lookup table. However, the formula below will use a fourth argument of True. This rare version of VLOOKUP tries to find the lookup value in the table. When it is not found, instead of returning #N/A, the formula will return the result for the just-smaller value.
The original formula in the first figure was 211 characters long. The new formula is 37 characters long. It is far easier to understand and maintain this formula.
Bill Jelen, aka MrExcel, has been a Microsoft Excel consultant for over 25 years. Read more of his expert Excel tips and tricks at his website, MrExcel.com.