8. VLOOKUP

Now finally, let’s assign a letter grade for the students based on their Current Total grade. Many professors will set a grading scale in which 90% or higher is an A, < 90% is a B, < 80% is a C, <70% is a D and < 60% is an F. Some professors also don’t care about minuses and pluses (or use other techniques for awarding them), so let’s ignore that here. We’ve already got enough work to do.

Are you imagining long nested IF functions? If >= 90, assign an A, else if >= 80 assign a B... That you can definitely do. But spreadsheets have a cool function you can use to do this more easily. It’s called VLOOKUP.

Let’s make a chart that shows the Grading scale and let’s add that as a column as well.

Let’s create a list of percentages and then a list of letter grades parallel to it from B10 to C14 and populate it similarly.

Now, here’s where the magic comes in. VLOOKUP is a really powerful function in spreadsheets. Also, just based on our asking around (including quite a few computer science professors!) not very many people seem to know about it. Maybe that’s because for the average person, it can seem complicated. But, with your understanding of computing concepts (like ifs, etc.) you should be able to mentally break out the work that a VLOOKUP does for you.

VLOOKUP uses 4 parameters, the value to look up, the range of a table, the column displayed, and whether or not it can look in a range.

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup).

When VLOOKUP looks for number values, it takes the first parameter and compares every value of the left side of the column to it. It sees if the value is smaller than the first parameter, then moves down the column. When it finds a value that is larger than the first parameter, it will return the cell in the row that isn’t larger than the first parameter but in the col_index column that isn’t larger.

Suppose I have two columns

10 Cat

20 Dog

30 Cow

40 Bird

50 Lion

If I have the number 38, it will return cow. Why? You’ll find out below when you try it yourself!

However, that’s not the only option for VLOOKUP. It can look up names and other thing for “exact” matches, too. If the range_lookup parameter is set to FALSE, then it will only look for exact numbers or letters/strings. Thus, this feature is really useful for combining different tables within a sheet and consolidating data.

In our case, let’s add Letter grade to the Q column. First let’s title it Letter grade (in cell Q1), and then use VLOOKUP. So the value we’re comparing to the grading scale table will be the current total. Therefore that’s the first parameter. Now, we need a set of data in a table. The grading scale table will be our set of data, which since it has the numerical numbers and the letter scale, numerical numbers will be column 1 and letter scale will be column 2. Finally, we need an output to display, and we’ll display column 2. As this is a range lookup, we don’t have to put any parameters for that as it will automatically be true.

We said VLOOKUP was complicated. Let’s walk through imagining what the computer code for VLOOKUP does so we can understand it a bit better. Let’s imagine looking up the letter grade for Chu, who currently has a 76.5%.

1. VLOOKUP will compare 0 to .76, and be like “ok, so this number is greater than 0, let’s keep F”.

2. Then it will check 0.6, and will compare 0.6 to 0.76, and it will confirm that 0.76 is bigger than 0.6, so it keeps D.

3. Then, it will move on and check 0.7, and will also confirm that 0.76 is greater than 0.7. and will save C to return.

4. Then, it will move on and check 0.8. Since 0.76 is not greater than 0.8, it will not save B, and will return (or output into the cell) C as the grade.

Back to the spreadsheet, let’s fill in cell Q2.

=VLOOKUP($P2, $B$10:$C$14, 2)

and look! It returns a C.

Now if you fill in (drag down) for the rest of the students, you’ll receive their grades as well.

There you have it. A full grade sheet that is similar to what many professors use.

[Q13.8.1] If I had letter grades listed in a column, how do I use VLOOKUP to provide the number that is associated to the grade in this spreadsheet?

<< Back Return to Intro to Spreadsheets >>