How to calculate student's grade in Excel using VLOOKUP Function

The LOOKUP (i.e. Vertical lookup) function looks for a value in the leftmost column of a table, and returns a value in the same row from a column you specified.

The syntax:    =VLOOKUP(lookup_value,table_array,col_index_num)

In our example, we will use the table screenshot below to calculate for student's grades

Tip:     Click here to download the Excel file. This will rather open the Excel file in Google Spreadsheet view 

Follow these steps to download the file onto your PC

• In the Google spreadsheet view, click on File menu, point to Download and select Microsoft Excel (.xlsx) file type to download the Excel file into Download folder on your PC.

Meanwhile, our Excel file contains two (2) sheets namely Data and Grade respectively. The Data sheet contains each students Total marks whiles the Grade sheet contains the grading table

The table below shows the grading system we will use to determine a student’s Grade and Remark respectively

When creating a grading system using VLOOKUP function, you only use the values at the left of the Marks column. i.e. 80, 70, 65, 60, 55, 50, 45, 40 and 0.  By default, the grading table must be rearranged from the lowest number to the highest number (i.e. from 0 to 80) with its corresponding grades and remarks information.

• In Grade sheet the table should look like the one screenshot below:

We will now assign a dynamic name to the grade table 

• In Grade sheet, highlight from 0 to Excellent only 
• Click inside the Name Box and type a name of your choice (without space). Example: Gradelist
• Press the [Enter] key to assign a dynamic (unique name) to this area

We will now determine the Grade for the first student in Data sheet.  To do this, follow these steps:

• Activate Data sheet
• Click in cell C4 and type:       =VLOOKUP(B4,Gradelist,2)
• Press the [Enter] key to complete the formula

Formula result: C4

Formula Explanation

• AutoFill cell C4 to cell C15

Try This

• In cell D4, determine the Remark for the first student

Formula result:     Credit

This formula is the same as the previous formula. This time it returns the text in the same row from the third column of GradeList corresponding to the grade

• AutoFill cell D4 to cell D15

Final Result:

Optional: Right-click on Grade sheet tab and select Hide to hide this worksheet

• You can edit the Total marks for the students or add new records to the table

Grab a copy of my new book Excel made Easy