Phone (01457) 858877 or email
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.
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.
Comments on this blog
This blog has 4 comments:
Hi this is exactly what I need to do, but am unsure how to apply it within an existing spreadsheet of exam scores.
Basically I have a list of 200 plus numeric scores, but need apply an alpha grade to each. How do I do this in and existing sheet?
Any help would be appreciated.
The process will be very similar to the one described under the heading "Using VLOOKUP to Categorise Values". The only difference is that you'll need to copy your formula down a column of 200 or more rows! To begin with, find a blank part of your spreadsheet and create the lookup table as described in the blog above. Remember to start with the lowest score and grade on the first row of your table, working your way towards the higher grades.
When you've done that it will help if you create a range name for the table, call it something like ScoreTable. If you're not sure how to do this you can read this article.
Now that you've created your lookup table head back to your list of scores. If we assume your list of scores starts in cell A1 you could select cell B1 and enter this formula:
Once you've done this all you need to do is copy the formula down the column of scores and you should find that it calculates the grade for each score.
Hope that helps.