In this video, we look at a few reasons why VLOOKUP is a better option than nested IF statements.
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 uses using VLOOKUP instead of nested IF statements.
If I change the sales number, all 3 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 become more complex, nested IF statements are a lot more flexible, but you may want to add white space to make them more readable.
The IF function can perform a logical test and return one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions....
VLOOKUP is an Excel function to lookup and retrieve data from a specific column in table. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. The "V" stands for "vertical". Lookup values must appear in the...
I sincerely want to register my profound gratitude to Dave and Lisa. There are many features so unique about the tutorials in Exceljet: the simplicity, down-to-earth approach and the connection between the instructor and the learner. -John