Vlookup Using Lists in Microsoft Excel

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 difficult multiple IF function

Using Vlookup with lists is helpful in many situations as mentioned in the Excel training video below:

Further reading:
Excel’s VLOOKUP Function

3 thoughts on “Vlookup Using Lists in Microsoft Excel

  1. Hello Sir,

    I already put a data on combobox but i want to used the combobox data in text box by using vlookup, i have a table for 10 columns first i want to select the data from combobox.

    If i select the combobox data all the excel sheet data will be field on each text box.

    Then each textbox will fill data for that affiliate as well as the date and color (Red, green).

  2. Hello Sir,

    I am using excel 2003 and using vba I have written the code to select multiple select from dropdown list into one cell with semicolon seperated values, I am trying to get the multiple selected values from that cell and get each code from lookup table where we have description and code and that the code available.

    Please help me in writing the function or how can i write for that code in vba and this need to reflect for the entire column.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.