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.