Search

# 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.

Other Excel articles you may also like:

FREE EXCEL BOOK

## Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

## 3 thoughts on “Avoid Nested IF Function in Excel…VLOOKUP to Rescue”

1. it’s so helpful and this video tutorials give me a boost of learning

2. I like the method

3. Thanks…this is useful!

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster