I didn't use arrays and simply wrote this for the first row and copied down:
=IF(B2,VLOOKUP($B$1,Sheet2!$A$2:$B$4,2))+IF(C2,VLO OKUP($C$1,Sheet2!$A$2:$B$4,2))+IF(D2,VLOOKUP($D$1, Sheet2!$A$2:$B$4,2))
Personally, I prefer to use the table referencing automation (see attached file). To do this, select each table and then choose a format from Home:Styles:Format As Table.
I like to rename the tables logically so using Formulas:Name Manager, I renamed your first table StudList and the other as SubjSum.
The above formula can then be rewritten for the first record in a more intuitive manner:
=IF([@English],VLOOKUP(StudList[[#Headers],[English]],SubjSum,2))+IF([@Maths],VLOOKUP(StudList[[#Headers],[Maths]],SubjSum,2))+IF([@Science],VLOOKUP(StudList[[#Headers],[Science]],SubjSum,2))
And due to another nice bit of automation, when you enter the formula, Excel copies it to all the other cells in that column automatically.
|