However, in some of the cases, this long formula can be cut short by using the VLOOKUP 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. 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.
