Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

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:

Formula:

=VLOOKUP(score,key,2,TRUE)

Example

Figure1. Example of calculating grades with VLOOKUP on Excel

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.

Figure2. 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:

Figure3. Example of changing respective values to calculate grades with VLOOKUP on Excel

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
I have a pivot table in which per order (on the rows) stands how much products they ordered per size (on the columns). I want to determine which combinations of quantities of sizes people order. And I want to count these combinations.
Solved by E. W. in 60 mins
I have 500 numbers in column A with aproximatly 15digits. In column B I have 5 numbers with 6 digits Can a find a formula wich can transfer all numbers from A column wich start with some 6 digitsa from B column
Solved by V. L. in 32 mins
I filtered data in column A (it is labeled ID #) of a data set. On my next worksheet, column A is also the ID #, but it is a different data set. I want to filter the ID #'s the same for the two sheets, but how do I do that being that I am working with two different sets of data?
Solved by V. H. in 22 mins
I have a sheet with 3 columns. First column is a code for records in column B (has 563 records). What I need is a formula to show me if what is in column C (has 4400 records) is in column B and if it is to take the code for that record. Example: Column A(code corresponding to column B): 12, 14, 15, 19 Column B(names): asd, adf, ade, aqw Column C(names): akd, adf, ade, anb, wgs I need something that would say, if record in column C (for example adf) is matching record in column B (adf) return the code from column A (the code corresponding to that record in column B),if not return blank.
Solved by C. J. in 8 mins
If the data in A matches the data in C, then I need the data in E to be placed in B
Solved by X. W. in 40 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc