Using Excel's vlookup function to copy grades into a packaged grade roster from the faculty center
This tip for submitting grades applies to the following scenario: You have a spreadsheet that includes student IDs, grades, and perhaps other information. This spreadsheet might have originated from Learn@UW. It doesn't matter where it came from as long as it contains student IDs and grades. One way to submit these grades is to export the packaged grade roster from the faculty center, copy the grades from your grade spreadsheet into the designated column in the grade roster spreadsheet, and then submit the latter in the faculty center.
The procedure below shows how to use Excel's vlookup function to automatically copy the grades from one sheet to the other.
Example class grade sheet
Example packaged grade roster sheet from the faculty center
Note that the two sheets do not need to be sorted in the same way.
Use vlookup to copy grades from the class grade sheet into the grade roster
The vlookup function is used in a new column (here column N) in the grade roster sheet to get the grade for the student from the other sheet.
The arguments to vlookup are:
- The name of the cell in the grade roster sheet containing the student ID to match.
- The range of columns in the class grade sheet that contains the student ID and the grades. It doesn't matter if additional columns are included in this range. The first column in this range must be the student ID column. One way to enter the range in this column is by navigating to the other sheet and dragging the mouse over the column headers.
- The index of the column within the selected range that contains the grade in the class grade sheet. The first column in the range is index 1, the second is index 2, and so on.
- Whether to do partial matches. Enter FALSE to get exact matches.
After entering the vlookup formula, pressing Enter should show the correct grade in the cell.
Copy the vlookup cell to the rest in the column
One way to do this is to hold the mouse over the lower right corner of the cell. The mouse pointer will become a plus. If you double-click, the cell should get copied to the whole column.
Copy the values of the vlookup cells into the grades column
Copy the cells and then use "Paste Special" and choose to paste the values. We do not want to copy the formulas, just the values.
The result:
Clear the vlookup column, now that it is no longer needed
Submit the completed sheet in the faculty center
In rare cases, an error may be reported during import about the roster not matching. In this case, it may be necessary to import section by section.