VLOOKUP Fuction

[knaimi]

Our school purchased a program that offers different courses for our students. My class has 15 students. When I download the scores in excel, the program doesn't separate the scores by teachers or by groups rather I get the scores of the entire school. the report even shows scores of previous classes that my students took. the report contains 5 columns:

Column A has the student name, Column B has the subject, Columns C has progress Scores, Column D has Overall Score, and Column E has the actual score. I have my roster printed in excel that has the same columns and I'm trying to transfer my students' scores in columns C, D, & E from the report to my list. I want to ensure that the name of the student (Column A) and the course (Column B) are both correct before transferring the score to that row. Can I do that with VLOOKUP? Or is there a better way to do it?

Please help,

 

[bellman101]

If each student and course combination can only appear once, then you're better using SUMIFS.

If the first row is row 2 and they are on a sheet called 'Scores', then try:
=SUMIFS(Scores!C:C,Scores!$A:$A,$A2,Scores!$B:$B,$B2)

You can then drag it to the right, and it should adjust itself for column D and E as well.

Cheers
JB

 

[knaimi]

That is not what I want to do, I want simply to transfer the three scores for each student from the report to my list.

 

[Joe4]

That is not what I want to do, I want simply to transfer the three scores for each student from the report to my list.

The formula bellman101 provided will do that. Have you tried it?

If the person only has one record for that subject, that it what it will return.
If they have multiple records, it will SUM the components of each one.
It sounds like that probably doesn't occur on your sheet. No matter, it should work either way.

 

[bellman101]

That is exactly what that formula will do.

E.G.

Student Subject Score
John Maths 70
Jim Geography 55
Joe IT 87
John Science 71
Jim Maths 42
Joe Science 91


You cannot do a VLOOKUP as it will only assess one column, but you can use sumifs. If you sum score where Student= Joe and Subject = Science, because that combination only appears once, it will return the correct score - 91 in this example.

 

[knaimi]

My sincere apologies to bellman101 and thank you both for your help. The formula did work ��

 

[bellman101]

No worries, glad to help.

Cheers
JB

 

[knaimi]

How can I adjust the formula so if the students doesn't have scores, N/A should appear?