Excel 2024: Replace Nested IFs with a Lookup Table

A long time ago, I worked for the vice president of sales at a company. I was always modeling some new bonus program or commission plan. I became pretty used to commission plans with all sorts of conditions. The one shown in this tip is pretty tame.

The normal approach is to start building a nested IF formula. You always start at either the high end or the low end of the range. "If sales are over $500K, then the discount is 20%; otherwise,". The third argument of the IF function is a whole new IF function that tests for the second level: "If sales are over $250K, then the discount is 15%; otherwise,".

These formulas get longer and longer as there are more levels. Once you get past a few levels, it is easier to move the logic to a lookup table.

Rather than use the nested IF function, try using the XLOOKUP function. In the formula below, all of the discount rules are shown in the table in E12:G18.

The Match_Mode argument with -1 will find the value just less than the search value. In the past with VLOOKUP and MATCH, your lookup table would have to be sorted. The following example shows that the XLOOKUP can perform an approximate match even if the table is not sorted.

Consider the table below. In cell C10, Excel will be looking for a match for $550,000 in the table. When it can't find 550,000, Excel will return the discount associated with the value that is just less in this case, the 20% discount for the $500K level.

What if your manager wants a completely self-contained formula and does not want to see the bonus table off to the right? Select the C10 cell. Click in the formula bar and select the characters F13:F18. Press F9 and Excel will replace the cell reference with an array constant. Repeat for the characters G13:G18. Press Enter. You can now delete the lookup table.

Bonus Tip: Match the Parentheses

Excel cycles through a variety of colors for each new level of parentheses. While Excel reuses the colors, it uses black only for the opening parenthesis and for the matching closing parenthesis. As you are finishing the formula below, just keep typing closing parentheses until you type a black parenthesis.

Thanks to Mike Girvin for teaching me about the matching parentheses. The lookup table technique was suggested by Danny Mac, Boriana Petrova, Andreas Thehos, and @mvmcos.