How can I score an assessment in Google Sheets where the answers to each question are weighted differently?

Question

I want to make an assessment tool in Google Sheets that asks a bunch of questions and provides a score. Each question has a set of possible answers, so they're all multiple choice. But each of the possible answers adjusts the score in a different way on a per-question basis.

Here's an example sheet with two tabs, one for the questions and one for the answer weights. The challenge I have is what to put in cells D2 and D3.

For example, "yes" might be worth +10 points on one question and -5 points on another. Similarly, one question may allow "yes"/"no"/"I'm not sure" and another may allow "yes"/"sometimes"/"no".

I'm not sure if Google Sheets contains the tools that'll let me do this in a reasonable way without resorting to writing App Script. It looks SWITCH might be useful, but I'm not clear on exactly how to wire things together.

Answer 1

Use following INDEX and MATCH formula:

=INDEX('answer-scoring'!$A:$G,
 MATCH($A2,'answer-scoring'!$A:$A,0),
 MATCH($C2,'answer-scoring'!$A$1:$G$1,0))

where index returns values from score table that matches both question and answer.

You can find it Here