How to replace nested IFs with VLOOKUP

You might build or inherit a worksheet that uses a series of nested IF statements to assign values of some kind. Many people use nested IF statements this way because the approach is easy once you get the hang of it. But nested IF statements can be difficult to maintain and debug.

Let's look at how you can use the VLOOKUP function instead.

Here we have the classic problem of assigning grades to scores. Each student in the list has a set of test scores that are averaged in column G. In column H, a formula uses a series of four IF statements to determine a grade based on the average. The formula starts with low scores and works up to high scores by using the "less than" operator.

Let's add another column that calculates the same grade using VLOOKUP.

The first thing we'll do is build a table we can use to assign grades. We'll need a column for scores and a column for grades. To make it easier to get the values we need from the existing formula, we'll convert one of them to text by adding a single apostrophe before the equal sign. Now we can see the formula while we work. We need to add a new row for each possible grade.

We can use the Format Painter to quickly apply formatting.

Now we have what we need to assign grades using VLOOKUP. VLOOKUP matches on the first column of a table. By default, VLOOKUP does not require an exact match, which is important, because we don't want to have to add a row for every possible score. However, the table must be sorted in ascending order.

Before we start using VLOOKUP, let's define a name for the table. This is not strictly necessary, but it will make our formula easier to read. Let's name the table "grade_key."

Now let's add our VLOOKUP formula. The first argument is the value we're looking up, which we get from column G. The second argument is the lookup table. The third argument is the column that holds the value we want. Because the grades are in the second column, we use the number 2.

VLOOKUP takes an optional fourth argument that controls exact matching. The default is TRUE which means "non-exact match." In non-exact match mode, VLOOKUP will match exact values when possible and the next lowest value when not.

When we enter the formula, we get our first grade. Now we can just copy the formula down the table.

You can see that we get the same grades, but with some nice advantages.

First, the formula itself is much easier to read. Also, the grade key is exposed on the worksheet for easy reference. Finally, the grade key itself controls the grades. We can easily change a score and get new grades. In addition, we can add new rows to the key and the existing formula "just works."

There's no need to wrangle an unruly herd of parentheses.

The next time you're facing a formula with nested IFs, consider using VLOOKUP instead.