Please see the attached excel file. Take a look at sheet 1 and sheet 2.
vlookup_multiple_values.xlsx

Thank you.

I have just come up with a solution.
vlookup_multiple_values.xlsx

But I just wonder if there is one formula (not two formulas spanning two cells)? Much simpler?

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.

Attached Files

vlookup_multiple_values (1).xlsx (12.3 KB, 11 views)