Lookup, match then sum to matrix .... is this possible?

[blackduck]

Hi, I have limited excel knowledge and I am not sure how to tackle this task. I have a survey that is for mentors and mentees wanting to participate in a mentoring program. I want to compare the responses from the mentees against the responses from the mentors to see how compatible the two people are and I want to put this in a matrix.

As an example, below is dummy data of what the survey would look like:

[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Person[/TD]
[TD="width: 64"]What[/TD]
[TD="width: 64"]Q1[/TD]
[TD="width: 64"]Q2[/TD]
[TD="width: 64"]Q3[/TD]
[TD="width: 64"]Q4[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]mentee[/TD]
[TD]happy[/TD]
[TD]sad[/TD]
[TD]blue[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]Mentee[/TD]
[TD]happy[/TD]
[TD]angry[/TD]
[TD]pink[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Mentee[/TD]
[TD]happy[/TD]
[TD]mellow[/TD]
[TD]green[/TD]
[TD]red[/TD]
[/TR]
[TR]
[TD]****[/TD]
[TD]Mentor[/TD]
[TD]angry[/TD]
[TD]sad[/TD]
[TD]purple[/TD]
[TD]pink[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]Mentor[/TD]
[TD]happy[/TD]
[TD]mellow[/TD]
[TD]red[/TD]
[TD]red[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Mentor[/TD]
[TD]angry[/TD]
[TD]angry[/TD]
[TD]red[/TD]
[TD]green[/TD]
[/TR]
</tbody>[/TABLE]

In the matrix i need to produce, I need to compare the survey results from the mentor against the mentee, if they answer questions the same way then they get a score - I then need to total that score over the entire survey. I would like a score of 10 if Question 1 matches, 10 if question 2 matches, 30 if question 3 matches and 20 if question 4 matches. So using the example above the matrix would need to look like this:

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]****[/TD]
[TD="width: 64"]Harry[/TD]
[TD="width: 64"]John[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]


Anyone have any idea how I could create this matrix using formulas? I will need to be able to look up names in the survey, then compare the results from a particular question based on the names, then return a number if the cells match, then add that all together in a final score. Open to any suggestions on how to handle this situation. Thank you

 

[Marcelo Branco]

Welcome to Mr Excel

I think the results are different from the ones you showed. See if this is what you want.


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[TD="bgcolor: #DCE6F1"]

K​

[/TD]
[TD="bgcolor: #DCE6F1"]

L​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Person​

[/TD]
[TD]

What​

[/TD]
[TD]

Q1​

[/TD]
[TD]

Q2​

[/TD]
[TD]

Q3​

[/TD]
[TD]

Q4​

[/TD]
[TD][/TD]
[TD]

Question​

[/TD]
[TD]

Q1​

[/TD]
[TD]

Q2​

[/TD]
[TD]

Q3​

[/TD]
[TD]

Q4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Sam​

[/TD]
[TD]

mentee​

[/TD]
[TD]

happy​

[/TD]
[TD]

sad​

[/TD]
[TD]

blue​

[/TD]
[TD]

green​

[/TD]
[TD][/TD]
[TD]

Points​

[/TD]
[TD]

10​

[/TD]
[TD]

10​

[/TD]
[TD]

30​

[/TD]
[TD]

20​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Luke​

[/TD]
[TD]

Mentee​

[/TD]
[TD]

happy​

[/TD]
[TD]

angry​

[/TD]
[TD]

pink​

[/TD]
[TD]

green​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Tom​

[/TD]
[TD]

Mentee​

[/TD]
[TD]

happy​

[/TD]
[TD]

mellow​

[/TD]
[TD]

green​

[/TD]
[TD]

red​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Jack​

[/TD]
[TD]

Mentor​

[/TD]
[TD]

angry​

[/TD]
[TD]

sad​

[/TD]
[TD]

purple​

[/TD]
[TD]

pink​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Harry​

[/TD]
[TD]

Mentor​

[/TD]
[TD]

happy​

[/TD]
[TD]

mellow​

[/TD]
[TD]

red​

[/TD]
[TD]

red​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

John​

[/TD]
[TD]

Mentor​

[/TD]
[TD]

angry​

[/TD]
[TD]

angry​

[/TD]
[TD]

red​

[/TD]
[TD]

green​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD][/TD]
[TD]

Jack​

[/TD]
[TD]

Harry​

[/TD]
[TD]

John​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

Sam​

[/TD]
[TD="bgcolor: #D9D9D9"]

10​

[/TD]
[TD="bgcolor: #D9D9D9"]

10​

[/TD]
[TD="bgcolor: #D9D9D9"]

20​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

Luke​

[/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD="bgcolor: #D9D9D9"]

10​

[/TD]
[TD="bgcolor: #D9D9D9"]

30​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

Tom​

[/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD="bgcolor: #D9D9D9"]

40​

[/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Table Question - Points in columns H:L

Formula in B11 copied across and down (gray area)
=SUMPRODUCT(--(INDEX($C$2:$F$7,MATCH($A11,$A$2:$A$7,0),0)=INDEX($C$2:$F$7,MATCH(B$10,$A$2:$A$7,0),0)),$I$2:$L$2)

Hope this helps

M.

 

[blackduck]

This is perfect - thank you so much for your assistance!!! I've tried it out and it works - you have saved me so much time. One more question - is there anyway to edit the formula so that if the cell is empty (lets say for question 1 sam and jack both leave the question blank) that the points are not assigned?:

[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"] Person
[/TD]
[TD="class: xl63, width: 64"] What
[/TD]
[TD="class: xl63, width: 64"] Q1
[/TD]
[TD="class: xl63, width: 64"] Q2
[/TD]
[TD="class: xl63, width: 64"] Q3
[/TD]
[TD="class: xl63, width: 64"] Q4
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"] Sam
[/TD]
[TD="class: xl63, width: 64"] mentee
[/TD]
[TD="class: xl63, width: 64"] [/TD]
[TD="class: xl63, width: 64"] sad
[/TD]
[TD="class: xl63, width: 64"] blue
[/TD]
[TD="class: xl63, width: 64"] green
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"] Luke
[/TD]
[TD="class: xl63, width: 64"] Mentee
[/TD]
[TD="class: xl63, width: 64"] happy
[/TD]
[TD="class: xl63, width: 64"] angry
[/TD]
[TD="class: xl63, width: 64"] pink
[/TD]
[TD="class: xl63, width: 64"] green
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"] Tom
[/TD]
[TD="class: xl63, width: 64"] Mentee
[/TD]
[TD="class: xl63, width: 64"] happy
[/TD]
[TD="class: xl63, width: 64"] mellow
[/TD]
[TD="class: xl63, width: 64"] green
[/TD]
[TD="class: xl63, width: 64"] red
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"] Jack
[/TD]
[TD="class: xl63, width: 64"] Mentor
[/TD]
[TD="class: xl63, width: 64"] [/TD]
[TD="class: xl63, width: 64"] sad
[/TD]
[TD="class: xl63, width: 64"] purple
[/TD]
[TD="class: xl63, width: 64"] pink
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"] Harry
[/TD]
[TD="class: xl63, width: 64"] Mentor
[/TD]
[TD="class: xl63, width: 64"] happy
[/TD]
[TD="class: xl63, width: 64"] mellow
[/TD]
[TD="class: xl63, width: 64"] red
[/TD]
[TD="class: xl63, width: 64"] red
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"] John
[/TD]
[TD="class: xl63, width: 64"] Mentor
[/TD]
[TD="class: xl63, width: 64"] angry
[/TD]
[TD="class: xl63, width: 64"] angry
[/TD]
[TD="class: xl63, width: 64"] red
[/TD]
[TD="class: xl63, width: 64"] green



[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Assuming data setup like post #2 , try this in B11 copied across and down
=SUMPRODUCT(--(INDEX($C$2:$F$7,MATCH($A11,$A$2:$A$7,0),0)<>""),--(INDEX($C$2:$F$7,MATCH($A11,$A$2:$A$7,0),0)=INDEX($C$2:$F$7,MATCH(B$10,$A$2:$A$7,0),0)),$I$2:$L$2)

M.