Manage Learn to apply best practices and optimize your operations.

Excel tip: Replacing nested IF formulas with VLOOKUP

Just because Excel users can nest up to 32 functions in a single formula doesn't mean it's the best idea. Learn how to simplify nested IF formulas.

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:

nested IF statements

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:

sales amounts

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.

Rates formula

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.

VLOOKUP function

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.

Dig Deeper on ERP accounting software