I have two excel workbooks with data on the same students as shown in attached image 1.
I want to merge both workbooks to create a more useful workbook for me as shown in image 2.
I figure the unique number assigned to each student is the correlating factor but not sure how to do the rest.
If you are able to help me out it would be very much appreciated and save me hours.
Thanks very much in advance.
You show workbook1 and workbook2. Generally data is in a sheet in a workbook and to give a proper example, the formula would need to refer to the sheet.
so assume Workbook1 is actually [Workbook1.xlsx]Sheet1
and Workbook2 is actually [Workbook2.xlsx]Sheet2
so assume you copy the "number" column from one of the worksheets and paste into column A of another worksheet.
then in B2 you could do this
if either the workbook1 actual name or the sheet1 actual name had a space in it then you would need to enclose in single quotes like this:
=IfError(Vlookup($A2,'[Workbook 1.xlsx]Sheet 1'!$A:$G,3,False),"")
Vlookup depends on the leftmost column in the lookup range to contain the value being looked up. In this case "Number". the third argument to Vlookup, the 3 in the example tells what column from the lookup range to return the value from the matched row. Since we want the value from column C of workbook1, and we are looking in A to G, we put in the argument 3
So in column G of the new sheet, you would want to look up Class from Workbook2. Then the formula would be
So we look in column B on Workbook2 for the match and return the value from column F which is the 6th column in the sheet, but the 5th column in the lookup range since we start in column B.
If you needed to retrieve a value from column A of workbook2 since it is to the left of the number column you would use index and match
Hopefully that gives you enough information to resolve your problem.
Note that if all the sheets are in the same workbook, then you would only need to use the sheet names:
Assume workbook2 is actually named Sheet2