As a quick recap:
The first formula is created with nested IF statements normally.
The second example is the same formula but formatted with line breaks to make it easier to read.
The third formula performs the same calculation, but it uses VLOOKUP instead of nested IF statements.
If I change the sales number, all three formulas calculate the same commission rate.
So, let’s look at some reasons whyVLOOKUPis a better choice in this situation.
The commission rates are not part of the formula, and neither are the sales thresholds.
That’s because VLOOKUP is using the commission table on the worksheet directly.
This makes the formula much shorter and easier to read.
More importantly, this means I can just edit the table if I want to change the commission structure.
In contrast, the nested IF formulas must be updated wherever they appear on the worksheet.
This advantage is even more apparent if I add or remove a tier in the commission structure.
While this is a simple operation in the table, it’s considerably more complex with nested IF statements.
Another advantage that VLOOKUP has is transparency.
