Friday, 22 October 2021

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. 

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

No comments :

Post a Comment

Thanks for your feedback. The elves will post it shortly.