What is (Purpose of ) VLookup function of MS Excel
VLookup (Vertical Lookup) is an useful function of Microsoft Excel which searches first column of a given range against the given value (lookup value) and returns one desired column value from the founded row.
To understand VLookup function more clearly we see one example. Let say School X is maintaining Students information in multiple sheets.
First Sheet (Master) contains students master data, like Student Number, Name, Father Name, Mother Name, Date of Birth, Class etc.
Second sheet (Marks) contains Students marks list which contains Student Number, subject wise marks, total and exam type.
Now Management wants Student Summary Report from Student Number, Student Name, Class, Total Marks and Exam from the columns which contains in both first (Master) and second (Marks) sheets. It is difficult to prepare this kind of report for large volume of data without using VLookup function.
How to use VLookup function of MS Excel
Vlookup Function contains four parameters.
1.Lookup Value : Value which is used to search in the first column of table array.
2.Table Array: Table or range (source of data) in which actual data to be searched exists.
3.col_index_num: Desired column number in the table or range which should display as result.
4.range_lookup: Optional parameter. True ( or 1) for Approximate match, False (or 0) for Exact match. If nothing is given By default it takes True ( or 1). Generally this parameter contains False (or 0), i.e. exact match. Read this to know more about fourth parameter of VLookUp function.
Now let us see how summary sheet should be prepared. First we have to find the column which is common in both the ranges. In our example we know Student Number is common field for both Marks and Master sheets. So we use Student Number as first parameter (look up value) in VLookup function . So first get the Student Numbers for which you want Student Summary Report.
If you observe the Master sheet the data is available from the Cell A3 to F7. So the range for Master sheet is Master!$A$3:$F$7. Same way if you observe the Marks sheet the data is available from the Cell A3 to F7. So the range for Marks sheet is Marks!$A$3:$F$7.
$ (Dollor symbol) should be used in range, otherwise if you copy the formula to another cells range will be changed, which leads in wrong range and thus inaccurate results.
Now observe how VLookup function gets the data from multiple ranges for each column.
Student Name: Student Name is available in Master Sheet. It is available after Student Number, that means at second position. So put the formula =VLOOKUP(A3,Master!$A$3:$F$7,2,0) in Student Name and drag the same.
Class: Class is available in Master Sheet. It is available after DOB, that means at second position. So put the formula =VLOOKUP(A3,Master!$A$3:$F$7,6,0) in Class and copy the same.
Total: Total is available in Marks Sheet. It is available after sub3, that means at fifth position. So put the formula =VLOOKUP(A3,Marks!$A$3:$F$7,5,0) in Total and copy the same.
Exam: Exam is available in Marks Sheet. It is available after Total, that means at sixth position. So put the formula =VLOOKUP(A3,Marks!$A$3:$F$7,6,0) in Total and copy the same.
If data is not available #N/A will appear as a result. For example For Student Number 6 there is no data for Marks and Master sheets. So #N/A is appeared as a result in above Student Summary Report.