Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
550 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Using the VLOOKUP Function in Excel to Calculate Exam Grades |
---|
The VLOOKUP function in Excel is perfect for categorising a continuous range of numbers. This topical example shows you how to calculate an exam grade based on a numerical score.
You might like to look at the more modern XLOOKUP function instead, though: The XLOOKUP function does what the VLOOKUP function does, but much better. |
The VLOOKUP function in Microsoft Excel allows you to look for a specific value in a table and to return some related information. There are two forms of the VLOOKUP function: one allows you to look for a specific item or code and find information about that exact item; the other allows you to look for a value and find out which band or category it belongs to.
With the A-Level and GCSE examination results released in the UK over the past two weeks, this seemed like the perfect time to show both types of VLOOKUP functions using the same data.
The basic syntax of the VLOOKUP function is shown below, followed by an explanation of what the arguments mean.
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
There are four arguments in total, but only the first three are required. The arguments are:
For our first example we're going to assume that we want to find what score we need to get in order to achieve a certain grade. This means we're going to give Excel a specific grade, such as B, and ask to see the score range for that grade.
The first thing we need to do is create the lookup table. The diagram below shows what it will look like (the information is based on data taken from the AQA GCE Mathematics Unit D02 paper).
Our table consists of two columns, the first contains the values we are going to be looking up, and the second contains the answers we want to get. The order of the rows in this table doesn't matter as we'll be looking for an exact match. We've also given our table a range name to make our formula easier to create.
In another part of the spreadsheet we can create our lookup system, as shown below:
We've created a cell for a user to type in the grade they want to get and given this a range name. The next job is to create the function to return the right answer.
The last step is to create the formula that will return the score needed to achieve the grade we're looking for. To do this, we'll enter the formula shown below into cell B2:
This formula says to look for the grade we're looking for (GradeWanted) in the table of grades (GradeTable), give us the answer from the second column of that table (2), and make sure you're looking for an exact match (FALSE).
We should now be able to type any grade into cell B1 and see the required scores appear in cell B2.
The finished lookup system.
The alternative way to use our VLOOKUP function would be to enter a numerical score and ask Excel to tell us what grade we'll get. This is slightly more difficult than the previous example because multiple scores can return the same grade and we definitely don't want to have to create a huge lookup table to handle all the possibilities. Instead, we'll tell our VLOOKUP function to behave differently when looking for answers.
To begin with we need to create the lookup table. We have to be a little more careful when doing this for a lookup that doesn't look for an exact match. The main things that we need to do are:
Following these rules, our table should look like this:
This type of table allows us to search for any score from as low as 0 and for Excel to find a match for us.
We can now create a similar lookup system to earlier, except this time we'll be typing in a score and getting Excel to tell us what grade we've achieved.
The final step is to create the VLOOKUP function return the right answer.
The key to creating this type of VLOOOKUP is in the optional fourth argument. In the previous example we used FALSE to tell Excel that we were not looking for an approximate match. In this example we need to do the opposite - we can either use the value TRUE, or miss out this argument altogether as shown below.
Here we've omitted the fourth argument altogether, although we could have used the value TRUE instead.
The function will now tell us what grade we achieved based on our score.
The finished VLOOKUP function.
Some other pages relevant to the above blog include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.