Excel 2024: Replace Nested IFs with a Lookup Table
July 15, 2024 - by Bill Jelen
![Excel 2024: Replace Nested IFs with a Lookup Table Excel 2024: Replace Nested IFs with a Lookup Table](/img/excel-tips/2024/07/excel-2024-replace-nested-ifs-with-a-lookup-table.jpg)
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 uses 9 different colors for parentheses. While the magenta color is used every 8th time, the color black is reserved for the first parenthesis and the matching closing parenthesis.](/img/content/2024/07/XLFig70.png)
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.
Tip
In Microsoft 365, you could use the following formula to solve the discount presented above: =IFS(B10>500000,20%,B10>250000,15%,B10>100000,10%,B10>50000,5%,B10>10000,1%,TRUE,0)
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.
This article is an excerpt from MrExcel 2024 Igniting Excel
Title photo by Maurice Schalker on Unsplash