Technology Tips & Tutorials

Microsoft Office 2007: Assigning letter grades using lookup table
February 2010

---

The VLOOKUP function searches for a value in the first column of a table and returns a value in the same row from a column you specify in that table.

In this example, class grades are on one worksheet. On a second worksheet in the same Excel file is a list of grade break points and their associated earned letter grades with lowest values at top, and highest scores/letters on the bottom. VLOOKUP compares the value of a select cell (e.g. the student's total course score) to the left-hand values in the grade break table, then returns the appropriate letter grade from column 2 in the grade break table..

The format of the function in this example is = VLOOKUP(score,table,letter_column)

Procedure:

On one worksheet, calculate a column of a final scores (normally as a percentage) for each student.

On a separate worksheet, Sheet 2 in this example, create a table that lists the letter grade break points (by percentage) and the corresponding letter grade for each interval. Put the lowest grade at the top of the sheet and the highest grade at bottom of the sheet, as shown below.

1. Highlight all the cells that make up the table (A1-14, B1-14).

2. Find the "Name" text box field above the table. The "Name" space is to the LEFT of the "fx" label: it may be necessary to drag the "fx" button to the right to open the "Name" space. Enter a name for the highlighted table. In this example the table is called "Grades".

Return to the sheet of student scores.

3. Click on the first empty grade cell. In this example, that is cell C2.

4. In the "fx" function entry box, type an equals sign (=), followed by the VLOOKUP formula. Here we are looking up the "score" stored in cell B2, in the table called "Grades", getting the letter grade stored in column 2 of the Grades table, and placing that value in cell C2.

5. To apply the formula in cell C2 to cells C3-C8, click on cell C2 to highlight it (get a dark square outline) then place your mouse on the fat bottom right corner of highlighted box and drag the mouse over the cells below. This copies the formula to the new cells and adjusts the formula for each new row number.