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.

1 Answer 1


Use following INDEX and MATCH formula:


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

You can find it Here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.