Using VLookup in Excel

Do you have related data which you have to regularly - and manually - add to a marking sheet? I use Excel to mark, and need both the student name, AND the student ID number on each marking sheet. I use two formulas to prevent me having to repeatedly add data to worksheet fields.

I use one Excel workbook, and add a new tab - worksheet - for each student. I have written about how to enter the student name onto the worksheet here. But basically I paste each student name into the tab name, and have a formula in the worksheet name field, as follows:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

The tab name then appears in the worksheet. Done.

However, I was still manually adding the student ID. So instead, I copied my semester list of student names (column A) and IDs (column B) into each marking workbook on a sheet named "Students", and, using a VLookup, have created a formula which appears in the Student ID field:

=VLOOKUP(C2,Students!A:B,2,TRUE)

Then I sorted the students into alpha-numeric order by name. NB: that is a very important step. The key data column in the VLookup must be in alphabetic order else the data association will appear to be randomly selected.

However, if for any reason we have another column that we want to sort on (such as a surname column instead of a first name column), we can change the last component of formula from 'TRUE' to 'FALSE' or '0' to avoid the sort, as:

=VLOOKUP(C2,Students!A:B,2,FALSE) if text, or =VLOOKUP(C2,Students!A:B,2,0) if a number

While I could have organised the VLookup so that my marking workbooks would refer back to my Master Excel workbook - i.e., not having to copy my list of students out into each marking workbook - I travel with my data. The drive letters change depending on where I am, and what device I am on. I would have been constantly having to repair my lost or broken links, so I decided it was better to have the data unlinked... and reduce problems.

Despite that small amount of double handing, I now have each student ID appearing once I create name each new marking sheet. One small piece of work done at the beginning of the semester saves double-handing for the remainder of my cohort marking.

Too easy.

Sam