Excel formula: Calculate grades with VLOOKUP

Microsoft Excel is a well-known tool for calculating grades with VLOOKUP function. VLOOKUP function is one of the commonly used functions on Excel, and it allows its user to calculate grades on Excel. The following comprehensive guideline discusses a simple systematic method, which anyone can use to calculate their grades using VLOOKUP function on Excel.

Calculating Grades with VLOOKUP using ExcelChat:

Figure1. Example of calculating grades with VLOOKUP on Excel

Formula:

=VLOOKUP(score,key,2,TRUE)

Explanation:

VLOOKUP function is one of the most used functions in Excel. It is fundamentally used for a vertical lookup for sorting out the values as per the criteria specified by a user. VLOOKUP function scans a prescribed value in a column of Excel Spreadsheet, and in return, it converts the value to the row, while taking its index_number position into consideration.

The generic formula given above requires a user to be mindful of the score, key, and the column where a value is located for ensuring optimal outcomes. Once a user has arranged the values into the formula—it should proceed to press ENTER for obtaining the desired result.

How does the formula work?

The generic formula of the VLOOKUP function in this particular case includes score, key, and column. Next, a user should include the range of scores and grade (B7:B11) to the equation. Also, a user should also select the corresponding cell number (B4) for postulating the equation. Last but not least, a user should include TRUE to the equation before enclosing it with a bracket. The ‘score’ serves as an important part of the formula. Using VLOOKUP formula will activate C4 for enabling it to calculate grades in the respective column.

Using VLOOKUP function allows a user to calculate grades on an Excel Spreadsheet easily. However, a user should simply configure a table, which serves as a ‘key’ (refer to the formula), scores in the left column and the corresponding grades in the right column. Also, you should sort out the table in an ascending order for acquiring optimal outcomes. Next, you should tweak the configuration of VLOOKUP function for enabling it to perform an approximate match on Excel.

Figure 2. Example of how to calculate grades with VLOOKUP on Excel

In the screenshot attached above, a user is required to target the value mentioned in B4 for calculating grades with VLOOKUP. In this case, B7:C11 serves as a grading key in the equation. Also, the given example comprises of the 2-column table, so a user would be required to insert (2) to the equation manually for configuring its setting. Also, the addition of TRUE to the equation results in the activation of an approximate match.

Once the approximate match mode is activated, the VLOOKUP function scans the selected keys, columns, and the validity of the equation for obtaining the desirable outcomes.

However, similar to most iterations of VLOOKUP function—a user cannot apply the same formula across the columns or rows. It is required to change the respective values of the equation accordingly, and it should refrain from dragging the formula across the column, as it could result in an error.

Have a look at the following example: