In this short video, we look at how to replace a typical nested IF formula with a VLOOKUP formula. Compared to nested IF statements, VLOOKUP is simpler and more transparent. It's also easier to adjust later. Once set up, you can change the formula's logic without even touching the formula itself. It just works.
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 see get the values we need from the existing formula, we'll convert the nested IF formula 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 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 add a row for every possible score. However, the table must be sorted in ascending order.
Before we starting using VLOOKUP, let's define a name for the table. This is not strictly necessary, but it'll 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
The IF function runs a logical test and returns 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. The IF...
VLOOKUP is an Excel function to look up data in a table organized vertically. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. Lookup values must appear in the first...
The Excel AVERAGE function calculates the average (arithmetic mean) of supplied numbers. AVERAGE can handle up to 255 individual arguments, which can include numbers, cell references, ranges, arrays, and constants.