New to using VLOOKUP and/or INDEX MATCH. Working with massive data set.

[jbigie]

Hi all,

I apologize ahead of time for the lengthy explanation. Since I am new here, no one knows the context of my questions, so I need a little exposition up front.

I'm entering my last year of Physics at my university, and starting work on my research project for my senior thesis. Since I am also majoring in Education, I am working with one of the Physics professors on his Physics Education research. We are doing educational data mining, and I am new to this sort of work, and have never used some of the advanced features of Excel that are needed. Right now, I am working on data of student interactions with a homework website. The site captures student names and IDs when they sign up with the access code and the course code provided by the instructor. However, the site is not connected to the university registration records, and cannot verify that they input the correct student ID, nor does it seem to check that they even input an ID.

Out of about 450 students, 87 of them either put in the wrong data (email address, the instructor ID code, and course ID code are some of what I've seen) or left the student ID spot blank, which is transmitted in the report of all their interactions. I need to fill in the missing student IDs with the correct information, so we can pass the reports to another professor in the department who will de-identify all the information and link the correct student with our existing data set from another site. We already have some data that started with login names and IDs, and that was scrubbed and given to the project as "Student 001", and so forth. We need to get the information from this homework site processed the same way, so that our existing "Student 001" has the correct interactions with the homework site added properly.

What I am specifically trying to do is copy the student IDs from one workbook with the names and IDs for the students who are registered for the course, and fill in each instance of the missing student ID next to the names on the main report. I attached a dummy data set with a few names and examples of the columns I am working with. In the real data, each student has between 700 and 1200 interactions with the site. I need to figure out a command that will look up the student name in the names and IDs list that is next to the blank space in the report, and fill the blank with the corresponding student ID. The report has the names in two columns, and the names list has the names in one column, (last, first) as shown in the example.

I got VLOOKUP to return the correct value in one cell. =VLOOKUP(C2&B2,'Student Names IDs'!A1:B8,2,TRUE) I tested it on another cell, with a different name, and it also worked there. Is there a way to make it automatically fill in every example of the same name? When I tried copying the VLOOKUP to the rest of the column, it automatically moved the table reference ("A1:B8" became "A2:B9") and I got a #N/A result. I want it to continue to move the C2&B2 down the columns, since that seems to work correctly. Adjusting each example of the formula will take forever with a data set this large.

Thanks in advance for the assistance.

[jbigie]

And I just found a page about using the $ to indicate absolute vs relative cell references and it works now. I don't know how to delete the post.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[jbigie]

Thanks for the info on marking it solved.