Sum Product, Index Match with Multipler effect

[dmessmer]

Hello all.. thanks for the help on this one, it has me stumped.

I'd like to do lookup where, based upon a cell, it checks a range of cells to see if they are equal to their corresponding cell, and if they are equal then it accounts for the 'multiplier effect'. Probably best shown in an example.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Fruit Answers
[/TD]
[TD][/TD]
[TD]Joe Smith
[/TD]
[TD]Joe Smith Multiplier
[/TD]
[TD]Cate Reed
[/TD]
[TD]Cate Reed Multiplier
[/TD]
[TD]Scores
[/TD]
[TD]Points
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Apple
[/TD]
[TD][/TD]
[TD]Apple
[/TD]
[TD]1
[/TD]
[TD]Apple
[/TD]
[TD]5
[/TD]
[TD]Joe Smith
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Orange
[/TD]
[TD][/TD]
[TD]Orange
[/TD]
[TD]2
[/TD]
[TD]Banana
[/TD]
[TD]3
[/TD]
[TD]Cate Reed
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Banana
[/TD]
[TD][/TD]
[TD]Banana
[/TD]
[TD]4
[/TD]
[TD]Orange
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Strawberry
[/TD]
[TD][/TD]
[TD]Strawberry
[/TD]
[TD]5
[/TD]
[TD]Raspberry
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Raspberry
[/TD]
[TD][/TD]
[TD]Raspberry
[/TD]
[TD]3
[/TD]
[TD]Strawberry
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Cell H2 should reference cell G2 and find 'Joe Smith' in cell C1. Then based upon this it should look at C2-C6 to see if it matches A2-A6. If any of those cells match, then it should add up the corresponding value in cells D2-D6. So since Joe Smith's answers matched all of the correct answers, D2-D6 are summed to equal 15.

H3 references Cate Reed & sees that only the only correct answer is E2. Therefore it only returns F2.

Really appreciate any help you're able to provide on this one!

 

[FDibbins]

Based on your sample data, try this...
=SUMPRODUCT(--($A$2:$A$6=INDEX($C$2:$F$6,,MATCH(G2,$C$1:$F$1,0))),INDEX($C$2:$F$6,,MATCH(G2,$C$1:$F$1,0)+1))
copied down

 

[dmessmer]

Ford, works like a charm!

One question though..

I thought I would be able to figure this part out once getting the main portion of the solution but I was mistaken... For those multipliers, if I actually want it to add up (X-the numbers posted in D2-D6).. is it possible to add this in?

For example: For that entry, If I'm inputting a vote of confidence instead of a multiplier, I would like the most confident entry (#1) to be worth the most amount of points (6-1). My least confident entry is #5 . So that one would be worth the least amount of points (6-5).

 

[FDibbins]

OK so based on your sample data, what would your answer look like - and why?

 

[dmessmer]

So for each person there are 5 entries total, so the maximum an entry can be worth is 5 points. H2 would recognize Joe's entries as worth the following points. D2 = 5, D3 = 4, D4 = 2, D5 = 1, D3 = 3. Cate's correct answers would be F2 = 1.


Basically I need to enter in a measure of how confident a person is with that answer. Ranging from most confident (1) to least confident (5). If they're most confident and correct that answer is worth 5 points. If least confident and correct that answer is worth only 1 point. My thought was just taking a hard entered 6 - their entry. e.g. D2's 'worth' would be: 6-1 = 5. But I'm not sure if that works with the below formula.