The Vlookup function in Microsoft Excel helps us to analyze data. Once we are able to access the value via Vlookup we can also perform calculations.
We enter data in an Excel worksheet like for example, the name of the student, his marks in different subjects and then we calculate the total, average marks and grades. Now the Principal of the school or the teacher wants to know what grade Tom got or what are his total marks. How can we find this out quickly? By using Vlookup in the following manner:
Let’s look at each of the parameters or arguments that we have to supply to Vlookup before it comes up with an answer:
Lookup_value: We want to lookup Tom’s total marks. The name Tom must always be located in the left-most or first column of our data range. For example we have the names of the students in column A and this is our first column. Since we are looking up a text or string value we enclose it in quotes. So now the Vlookup function looks like this:
Table_array: It is our complete data from which we wish to extract the information. Therefore, logically, if we wish to know Tom’s total marks we need to have at least two columns – one with names of the students and two the total marks, isn’t it? So now our function looks like this if our data range or table_array is in A5:G15:
Column_Index_num: Now we need to tell Vlookup where the total marks column is located. In our case the the first column_index_num is column A. The total marks are in column E. Therefore the column_index_num of column E becomes 5. Column A is designated as column_index_num 1, Column B is as 2, Column C is as 3, Column D is as 4, column E is 5 and so on. Therefore our vlookup function now takes the form:
[range_lookup]: Now comes the last argument called [range_lookup]. Actually Vlookup just wants to know whether your data is sorted or not and whether you wish to find an exact match or an approximate match. In our case our data is not sorted and we wish to know, of course, the exact total marks of Tom. So we select ‘FALSE’. This is the situation in most cases of data analysis. When we lookup somebody’s phone number from a telephone directory we always wish to find his exact phone number, isn’t it? So now our vlookup function looks like this:
The moment you close the bracket and press enter you get 216 as the answer which is correct. Please remember we are talking about extracting such data from large volumes of data!
In a similar fashion you can locate the price of an item from your Excel spreadsheet data and perform calculations like reducing it by 10% as way of discount before displaying it.
In summary, Vlookup is an excellent function in Microsoft Excel that helps us to locate a specific value from a large data range and also perform calculations if required.
View the Excel training video: