Avoid Nested IF Function in Excel…VLOOKUP to Rescue

A lot of times people ask me to help them in rectifying an error in their nested IF function. And sometimes their formula is bigger than this blog post (seriously! not kidding).

However, in some of the cases, this long formula can be cut short by using the VLOOKUP function.

Avoiding Nested IF Function

Consider a scenario as shown below. You have a list of students and their marks in an exam. Now you need to assign a grade to each student, based on predefined criteria.

Something as shown below:

Now one way is to write a long nested IF function and waste your time. Other, more time efficient, is the VLOOKUP way.

Vlookup to Rescue

To use VLOOKUP we need to slightly modify the criteria table, and make something as shown below

A prerequisite for this method is that criteria numbers in column I should be sorted in ascending order. The VLOOKUP method would not work if this list is not sorted.

Now use the below formula:

=VLOOKUP(C3,$I$3:$J$10,2,TRUE)

While this is our same standard VLOOKUP formula, notice it has an argument ‘TRUE’ at the end, which means an approximate match.

This means that when the marks are less than 30, VLOOKUP returns the grade for 0, which is F, but when it is 30, it will return E. Similarly, when marks are between 30 and 40, it returns grade E, but when it becomes 40 (or between 40 and 50) it returns grade D.

This is definitely a time saver, and easy to use than the long nested IF Function.