Use VLOOKUP to help manage your gradebook

VLOOKUP is a formula that allows you to “look up” certain data and retrieve it into another table, by row. This function can be especially useful when you have a well-structured table with information organized by rows (such as a gradebook)!

Here’s an example of how we use VLOOKUP for grading: We currently use a classroom response system in our mass lecture classes to encourage attendance. Each class has over 450 students responding to questions throughout the class period, and we award points based on correct answers. At the end of each class period, we download the classroom response system results into an Excel spreadsheet, but need to bring these grades into our gradebooks, which are set up with 12 sections of 40–50 students. We can easily sort out student grades by section using the VLOOKUP function.   

Below are two screenshots, one showing the grades for the entire class (from the response system) and the other showing part of a gradebook for one section. Say that you need to move the data in the Totals column from the class response system file to the Points column in the section’s gradebook. Here’s how you’d use VLOOKUP to do that:

Step 1: Highlight the cell to start the formula. Put your cursor in the “Points” column in the first student’s row (cell D2).

Step 2: Build the first VLOOKUP formula. All formulas begin with an equal sign ( = ).  These are the components of the VLOOKUP formula:

VLOOKUP(lookup_value, table_array, col_index_num, [range-lookup])

• Lookup_value: The value you want to look up; this is the student’s email address in Column C (cell C2)
• Table_array: Where to look for the data; this is the entire listing (or export file) of student grades from the response system (cells A2-B21)
• Col_index_num: This indicates the column number containing the value to retrieve. In this example, enter 2 or column B.
• Range_lookup (optional): This returns either an approximate match (true) or exact match (false).

Keep in mind one limitation of VLOOKUP: the column being searched needs to be to the left of the column with the data being returned.

Below are the steps to complete the formula.

Step 2.1: With the cursor in cell D2, type =VLOOKUP(

Step 2.2: With the mouse, click on the first email address (cell C2). Add a comma after the C2 in the formula: VLOOKUP(C2

Step 2.3: Go to the export file and with the mouse highlight the entire area. Before you enter a comma to move on, you will need to “lock” the values (A2:B21) in place. Otherwise, when you copy the formula down in the gradebook, these values will change. To lock these values in place, use a “$” sign in front of each variable. Add a comma at the end. Your formula will look like the one below.

=VLOOKUP(C2,ExportFile!$A$2:$B$21,

Step 2.4: Enter “2” because you want to look up data in column 1 (email) and bring back the grade (in column 2). Add a comma.

=VLOOKUP(C2,ExportFile!$A$2:$B$21,2,

Step 2.5: Type “true” if you need an approximate match. Type “false” if you want an exact match. In this case you’d want an exact email match, so type in “false.” Then type a closed parenthesis.

=VLOOKUP(C2,ExportFile!$A$2:$B$21,2,false)

Step 2.6: Hit enter and the formula is set.

Step 3: From here your cursor is still in cell D2. Copy the formula all the way down your list by double clicking on the little green square on the lower right-hand corner of the cell.

Below is how your file should look, with all of the grades for this section inputted from the other sheet:

VLOOKUP is an excellent, time-saving function that can help you manage your gradebook quickly and efficiently, especially for larger classes. It’s is also an excellent function to teach your students, if they need to bring information from one Excel file to another. The VLOOKUP function works on both Windows and Mac.

Editor’s note: If you have Excel 365, you may want to try the new XLOOKUP function as well. Learn more in this article.

— Wendy Tietz, CPA, CGMA, Ph.D., is a professor of accounting at Kent State University in Kent. Ohio; Jennifer Cainas, CPA, DBA, is an instructor of accountancy at the University of South Florida in Tampa; and Tracie Miller-Nobles, CPA, is an associate professor of accounting at Austin Community College in Austin, Texas. See their site AccountingIsAnalytics.com for resources they have developed for teaching data analytics in introductory accounting. To comment on this article or to suggest an idea for another article, contact senior editor Courtney Vien at Courtney.Vien@aicpa-cima.com.