Exceljet

Quick, clean, and to the point

This video comes from our online video training for Excel.

Why VLOOKUP is better than nested IFs

Summary 
In this video, we look at a few reasons why VLOOKUP is a better option than nested IF statements, when you need to assign a commission rate (or a grade) based on an arbitrary value.
Video Transcript 

Some reasons that VLOOKUP is superior to nested IFs. For context, see How to make Nested IFs easier to read. Also see how to replace a nested IF with VLOOKUP.

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.

 

Author 
Dave Bruns
The tutorial videos are superb! - Bernadette
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course