We have learnt how to use Vlookup and Vlookup with named ranges. Now we learn how to use Vlookup with lists. What is the main advantage of using Vlookup with lists? We can avoid large nested If functions especially if we hundreds of options.
In the example discussed in the video we will assign grades to students based on their total marks to avoid the use of multiple IF functions.
Implementing Vlookup using lists:
- We create a list in a new sheet or another area of the sheet that holds our data. Our data may look like this:
- We select the complete data from 0 to A and give the range B3:C7 in Sheet3 the name ‘grades_lookup’. We notice that the data is arranged in ascending order from 0 to 80 in column B
- Now we go to the sheet1 which has our data and enter the formula ‘=VLOOKUP(F5,grades_lookup,2,TRUE)
- The cell F5 has the total marks, our lookup value. ‘grades_lookup’ is our table array, 2 is the column number in our table array ‘grades_lookup’ which has the assigned grades and TRUE is the parameter for the range_lookup, a boolean value. Also we had created a table or list with ascending values of the average marks. If we omit TRUE our function will still work because TRUE is the default value anyway.
- You’ll also notice that we can create many more grades if we like and use the list conveniently
- Finally we do an Autofill to get all the grades. In case there thousands of students in your Excel worksheet we can double to do the Autofill quickly
- Did you notice that we got the same results with the difficult multiple IF function
Using Vlookup with lists is helpful in many situations as mentioned in the Excel training video below:
Excel’s VLOOKUP Function