In our last video, we used nested IF statements to calculate a commission rate based on a sales number. 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 why VLOOKUP is a better choice in this situation. First, notice that the VLOOKUP formula, unlike the nested IF formulas, doesn’t contain any actual data. 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 other words, I don’t need to edit the formula to change the rules that determine the commission rate. 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. To understand the commission structure, we just need to consult the table; there’s no need to click into the formula and study the conditions. And, if for some reason we don’t want the table to be visible on the main worksheet, I can just move it to another sheet and even hide that sheet if needed. So, to summarize, for simple nested IF statements that calculate a value based on one condition you should use VLOOKUP. If conditions are more complex, nested IF statements are a lot more flexible, but you may want to add white space to make them more readable.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
