Index and Match to Combine Data from Several Workbooks

Hi IrrepressibleXL,

For ease of practical solution, can you move all the required sheets from different workbooks to an single workbook so that LINKING may not cause issue. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi Matt,

I guess you are not getting me correctly..I am suggesting you to, just for getting solution and for the time being, have one workbook only with all the required sheet (though they are separate workbooks originally) and when you are satisfied with the solution you can have those sheets separated and change the reference of the formula later.

To more simply this, let me give you a example... if I want a solution from forum, where I need sheet 1 data of workbook A to be vlooked upon from sheet 7 of workbook B.... I'll move the sheet 7 from workbook B to workbook A and will post that one workbook to forum to do the formulation for me and then when I am satisfied with the solution, I'll edit the formula and have that reference to separate workbook on my pc....

this will avoid the encounter with linking issues.

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi Hi IrrepressibleXL, give this a try.

Firstly open all 3 workbook. In Overview b1 enter,

=COUNTA('[Class 1 Scores.xlsx]Sheet1'!B6:B100)

Note; Change Class 1 Scores.xlsx to your file name. to get name easily, type = in b1 then activate another file & click any cell, then just change the cell reference. In C1 use same formula just change the file name.

In B4, with CTRL+SHIFT+ENTER, rather than just ENTER

=IF(ROWS(B$4:B4)<=B$1,INDEX('[Class 1 Scores.xlsx]Sheet1'!B:B,SMALL(IF('[Class 1 Scores.xlsx]Sheet1'!B$6:B$100<>"",ROW(B$6:B$100)),ROWS(B$4:B4))),IF(ROWS(B$4:B4)<=B$1+C$1,INDEX('[Class 2 Scores.xlsx]Sheet1'!B:B,SMALL(IF('[Class 2 Scores.xlsx]Sheet1'!B$6:B$100<>"",ROW(B$6:B$100)),ROWS(B$4:B4)-B$1)),""))

This will return all the names from 2 workbooks. (Assume you will never have duplicate names)

In C4,

=IF(B4="","",IF(ISNUMBER(MATCH(B4,'[Class 1 Scores.xlsx]Sheet1'!$B:$B,0)),'[Class 1 Scores.xlsx]Sheet1'!C$2,IF(ISNUMBER(MATCH(B4,'[Class 2 Scores.xlsx]Sheet1'!$B:$B,0)),'[Class 2 Scores.xlsx]Sheet1'!C$2,"")))

This will return their classes

In D4, then copy & down & across.

=IF($B4="","",SUMPRODUCT(('[Class 1 Scores.xlsx]Sheet1'!$B$6:$B$100=$B4)+0,'[Class 1 Scores.xlsx]Sheet1'!C$6:C$100)+SUMPRODUCT(('[Class 2 Scores.xlsx]Sheet1'!$B$6:$B$100=$B4)+0,'[Class 2 Scores.xlsx]Sheet1'!C$6:C$100))

Assume all the test numbers are in same columns in both files. C:L

Hope this helps.