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:
Average Marks |
Grade |
0 |
Work Harder |
50 |
D |
60 |
C |
70 |
B |
80 |
A |
- 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
diffifult multiple IF function
Using Vlookup with lists is helpful in many situations as mentioned in
the training video above.