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:

Person	What	Q1	Q2	Q3	Q4
Sam	mentee	happy	sad	blue	green
Luke	Mentee	happy	angry	pink	green
Tom	Mentee	happy	mellow	green	red
****	Mentor	angry	sad	purple	pink
Harry	Mentor	happy	mellow	red	red
John	Mentor	angry	angry	red	green

<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>

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:

	****	Harry	John
Sam	10	10	10
Luke	0	10	20
Tom	0	30	0

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

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.

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

1​

Person​

What​

Q1​

Q2​

Q3​

Q4​

Question​

Q1​

Q2​

Q3​

Q4​

2​

Sam​

mentee​

happy​

sad​

blue​

green​

Points​

10​

10​

30​

20​

3​

Luke​

Mentee​

happy​

angry​

pink​

green​

4​

Tom​

Mentee​

happy​

mellow​

green​

red​

5​

Jack​

Mentor​

angry​

sad​

purple​

pink​

6​

Harry​

Mentor​

happy​

mellow​

red​

red​

7​

John​

Mentor​

angry​

angry​

red​

green​

8​

9​

10​

Jack​

Harry​

John​

11​

Sam​

10​

10​

20​

12​

Luke​

0​

10​

30​

13​

Tom​

0​

40​

0​

14​

<tbody>
</tbody>

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?:

Person	What	Q1	Q2	Q3	Q4
Sam	mentee		sad	blue	green
Luke	Mentee	happy	angry	pink	green
Tom	Mentee	happy	mellow	green	red
Jack	Mentor		sad	purple	pink
Harry	Mentor	happy	mellow	red	red
John	Mentor	angry	angry	red	green

<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[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.