Rollover - Using VLOOKUP to add student numbers

All student numbers need to be accurate and up to date on the DCS in order to complete rollover. We recommend leaving student numbers as 0 until the end of the academic year, and then updating these in bulk using the Bulk Course update function. 

If you have your student numbers listed on a separate spreadsheet, you can use the VLOOKUP function on Excel to quickly and easily update your student numbers. 

To do this, first generate a Student Numbers CSV prefilled with current information from the DCS: 

Once you have downloaded this CSV, open it alongside the file containing your student numbers:

To easily pull your student numbers from one file to next, use VLOOKUP. 

VLOOKUP has the following syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

This is what this means in more detail: 

lookup_value - the value that links the two spreadsheets. In this instance, we want to return student numbers based on course codes, so the lookup_ value is A1. 

table_array - the range of cells we are searching for our lookup_value. It's important to note that the lookup value must be the first column of the range of cells selected. So in this case, we want to select everything from row C onwards:

You can just highlight this range when filling in the formula.

col_index_num - the column number (counting the course code column as 1) that contains the value you want to return. In this case, we want to return the Number of students, so the col_index_num is 3. 

[range_lookup] - this can either be True or False. If True, the VLOOKUP will return an approximate match. If False, VLOOKUP will return only an exact match. For our purposes, we want to set this as FALSE. 

Our final formula looks like this: 

=VLOOKUP(A1,[Book1]Sheet1!$C$1:$E$11, 3, FALSE)

Enter your VLOOKUPFORMULA into C1, and then drag this down to pull across your student numbers:

If student numbers can't be found for a course code, you will see an #N/A value:

Once you completed the VLOOKUP, all you need to do is copy and paste your student numbers from Column C to Column B. 

Make sure you paste as 'Values only', otherwise Excel will think you are trying to copy the formula rather than the numbers. Once you have done this, you can delete the third column:

Any questions please don't hesitate to contact us at [e-mail] and we'll be happy to help.