Over the next few months students at schools, colleges and universities around the world will be sitting exams. Here’s a short tutorial that will save some time for those who will mark those exams. Even if you’re not a Teacher, with a little thought, I’m sure you can adapt this example for your own situation.
In A2:B6 we have the available grades and the score/mark that will be required to achieve each grade. So…
- Students achieving 0 – 20 receive grade E
- Students achieving 21 – 30 receive grade D
- Students achieving 31 – 40 receive grade C
- Students achieving 41 – 70 receive grade B
- Students achieving 71 – 100 receive grade A
As each exam is marked, the student’s name and mark is entered into columns D and E.
The cells in column F all contain formulas which calculate the grade based on the score. The formula in F2 (which is copied down F3:F8 is =VLOOKUP(E2,$A$2:$B$6,2,TRUE)
What does this mean?
The formula looks in E2 and sees the number 32.
It then looks down the first column of the range A2:B6 until it finds a matching value.
In this case there is no matching value (32 does not appear in A2:A6) but because I have used TRUE as the fourth argument, Excel looks for the largest value that is less than 32 – which in this example is 31.
If I had wanted to find a matching value, I would have used FALSE as the fourth argument of the formula and Excel would, in this case, have returned #NA as the answer – because there is no matching value.
Note that if you are using TRUE as the fourth argument, the items in the first column (A2:A6 in this example) must be in ascending order (lowest to highest, A-Z if text values)
So at this point Excel is looking at A4. It then looks across the same row and stops at column B. Why? Because in the formula, I used 2 as the 3rd argument. This means “in the range A:B (the second argument in the formula), column B is the 2nd column and it is this column that contains the answer”).
Excel is now looking at B4 which contains the value C. This is the result of the formula and therefore that is what is displayed in F2.