Once you learn how to build an IF statement you may think that this is the best way to build complex formula. However, there are often better ways. One example is how to avoid multiple IF statements. Commonly these are referred to as nested IF’s and are a source of confusion (in building and reviewing) and errors. So is there an alternative to multiple IF statements.
Calculate a sliding scale with Nested IF’s
Nested IF’s are often used in sliding scales.
So below the tax tables of South Africa have a different fixed component depending on what your salary is. The problem is that you have to build a complex IF function to tell Excel what it must do between each band. So what must it do if it is between the band 708 310 and 1 500 000. Notice how many levels there are and how many brackets are required to close the function.
You need an IF function for every band. This is difficult enough to build, but what happens if someone wants to add a band?
The best alternative to multiple IF statements
You may be surprised to learn that the best alternative is VLOOKUP. If you don’t know VLOOKUP properly, then learn more on the VLOOKUP tutorial.
In this case you need to understand the last part of the VLOOKUP. Most people who learn VLOOKUP are told to
‘just put false or 0 at the end’.
They don’t know why.
The 0 or false at the end tells VLOOKUP it must find an EXACT match. In the example above this would mean that with a salary of 250 000, Excel will return an error as there is no number that says 250 000. There is a 189 880 and a 296 540, but no 250 000. An exact match requires exactly that, an exact match.
This is where the true option (or 1) comes in. This tells Excel to find the closest match (Microsoft’s wording).
Notice below how much shorter the formula is compared to the nested IF. Which formula do you think will be more prone to error and difficult to change?
The VLOOKUP true tells Excel to run down the table and find the best fit for the number. That is why the one rule with a TRUE option is that the table must be sorted in ascending order.
The real benefit comes when you need to add new bands. With the nested IF you would need to re construct the formula. With VLOOKUP, you just need to add in the new rows in the correct place. Excel will do the rest.
As mentioned above, to get a good understanding of how VLOOKUP works (exact and approximate options) look at our VLOOKUP tutorial.