Using the Vlookup function in Microsoft® Excel 2010

Have you ever wondered what you would do if you had a humungous amount of data and you had to search for multiple values within that pile? Use the search function and retrieve the data you want? Imagine this scenario: You have the details of a group of students and their scores and you need to search for the scores in the grades table and grade them accordingly. How would you do it? vlookups makes it easy.

Vlookup is a function that returns a value from a range of data. The syntax for the vlookup function is:

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Let us try to understand what the above syntax means. The ‘lookup_value’ in the above syntax is for the value that you want to search in the table and ‘table_array’ is for the range of the table that contains both the value and the related value to return. The ‘col_index_num’ is for number of the columns whose value you want returned.

Now, let us use the above syntax on a table and see what results we get. You have the details of students and their scores in table ‘A’ and the grading details in table ‘B’.

The vlookup function will then pick the scores of students from table A, match them against the scores in table B and automatically populate the ‘Grade’ column of table A with the corresponding grade.

So here, we are searching for the score of Smith in the grade table and then populating the grade from that table depending on his score. Similarly, when we use the same formula for the rest of the students their grades would be picked up the grade table.

It’s as easy as that, vlookup can be a godsend when you’re dealing with data much larger than this (as is the case in real world scenarios). Imagine a scenario where you have thousands of students and you need to grade them. You will thank Excel 2010 for saving your time and sanity.