How to replace nested IFs with VLOOKUP

But nested IF statements can be difficult to maintain and debug.

Let’s look at how you might use theVLOOKUP functioninstead.

Here we have the classic problem of assigning grades to scores.

Let’s add another column that calculates the same grade usingVLOOKUP.

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.

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.

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 ourVLOOKUP formula.

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’re free to 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.