VLOOKUP Function Tutorial

Another chapter of my Master’s degree journey has opened, the second semester. One of my subjects is Basic Computer Application where I need to learn “more” about basic applications in computer.

One of the tutorial requests is how do you look up a value on one worksheet and use it on another worksheet.  So what we will be going to do is to convert the student’s raw score to its equivalent College Rating.

• As we can see, we have here the student’s raw scores from the three examinations, computing the average we have the formula =AVERAGE(C5;D5;E5), just click on the correspondent cells.

• We have now computed the average; the next question is how do we convert it to its equivalent college rating? For example a grade of 80-85 is equivalent to 1.75.

When working with student grades, of course we don't want to create a lookup table where we have to enter every possible average. Instead, our lookup table should have the starting average for each grade. Based on the table below, all averages of 96 or over should receive a grade of 1.00.

THE LOOKUP TABLE

In this example, the lookup table is created on another sheet. To create the lookup table:

1. In the first column, enter the minimum average for each grade.
2. In the second column, enter the matching Grade for each average.
3. Sort the averages in ascending order. 

THE REPORT OF GRADES

The scores are entered on another sheet (in my example, I named the lookup table as GRADES and the other one as GRADES REPORT), where a VLOOKUP formula calculates the final grade.

CALCULATING THE FINAL GRADE USING THE VLOOKUP FORMULA

Here are some steps on how to use the VLOOKUP formula in calculating the final grades of the students.

Step 1: Click INSERT and go to FUNCTION.

This box will appear. On the formula box, type the formula for VLOOKUP which is =VLOOKUP(

STEP 2: The syntax for the VLOOKUP function is: =VLOOKUP(searchcriterion;array;index;sortorder)

wherein: 

search criterion	-	The value that you want to search for
array	-	The array of data, that is to be searched for the lookup value. The Vlookup function searches in the left-most column of the array
index	-	The column number of the supplied array, that you want to return a value from
sort order	-	An optional logical argument, which can be set to TRUE or FALSE, meaning : TRUE - Find the closest match below the search criterion if the exact value is not found (Note: with this option, the left-hand column of the array must be in ascending order) FALSE - Find an exact match to the search criterion - if an exact is not found, the function returns an error If the [range_lookup] value is omitted, it takes the default value of TRUE

STEP 3: Given the above information, we can now provide the corresponding values. The formula should be =VLOOKUP(F5;Grades.$A$2:$B$8;2;TRUE, then click OK.

HOW IT WORKS?

1. In the VLOOKUP formula, the first argument, F5, is the value that we want to look up in the Grades table.
2. The second argument, Grades.$A$2:$B$8, is the location of the lookup table. The reference is absolute $A$2:$B$8, instead of relative, so that the VLOOKUP formula can be copied to other cells, and the table reference won't change.
3. The third argument, 2, is the column number in the lookup table, where the Grades are located. When an average match is found in the first column, the equivalent grade from the 2nd column will be returned.
4. The fourth argument, TRUE, means that an approximate match is okay. This is the default setting, and if you omit TRUE, you'll get the same result from the formula.

STEP 4: After computing the final grade or the equivalent grade of the average, drag the formula down to the remaining cells in the grades report.