Title: How to Score Google Form MCQ Responses Where Each Option Carries A Different Score Using a Scoring Map in Google Sheets

I'd been wondering if Google Response sheets from Google Forms can be used to score MCQs where each option has different scoring instead of the one just one correct answer and the rest are incorrect. I finally discovered the solution. 

This guide walks you through setting up an automatic scoring system for multiple-choice questions (MCQs) in Google Sheets using a separate scoring map. It is ideal for forms where each option carries a different score, and the response sheet contains full-text answers instead of option labels like A, B, C.

Step-by-Step Instructions

Step 1: Design Your Google Form

• Create MCQ questions with clearly worded options.

• Let the form store full-text responses (not A/B/C).

• Do NOT rely on Google Form's quiz scoring, as we'll be doing manual scoring in Sheets.

Step 2: Submit Sample Responses

• Submit 2–3 sample responses so your response sheet gets created.

Step 3: Open the Response Sheet

• Open the linked Google Sheet via Responses > View in Sheets.

• Identify which column holds which question.

Example layout:

C = Q1 Answer
D = Q3 Answer
E = Q2 Answer

Step 4: Create a New Sheet Tab Called "Scoring Map"

Add a new tab and name it Scoring Map.

Create 3 columns:

Question	Full Answer	Score
Q1	I adapt quickly.	2
Q1	I take some time to adjust.	1
Q1	I feel upset and avoid change.	0
Q2	I try different solutions.	2
Q2	I wait for help.	1
Q2	I give up easily.	0
Q3	I listen and improve.	2
Q3	I defend my actions.	1
Q3	I ignore it.	0

Step 5: Add Score Columns in Response Sheet

In your Form Responses 1 sheet, insert new columns:

F = Q1_Score
G = Q3_Score
H = Q2_Score
I = Total_Score

Step 6: Insert Formulas

In F2 (Q1_Score):

=IFERROR(VLOOKUP(C2, FILTER('Scoring Map'!B:C, 'Scoring Map'!A:A="Q1"), 2, FALSE), 0)

In G2 (Q3_Score):

=IFERROR(VLOOKUP(D2, FILTER('Scoring Map'!B:C, 'Scoring Map'!A:A="Q3"), 2, FALSE), 0)

In H2 (Q2_Score):

=IFERROR(VLOOKUP(E2, FILTER('Scoring Map'!B:C, 'Scoring Map'!A:A="Q2"), 2, FALSE), 0)

In I2 (Total_Score):

=SUM(F2:H2)

Drag each formula down to fill for all response rows.

Final Scoring Output Example

Email	Q1 Answer	Q1_Score	Q2_Score	Q3_Score	Total_Score
alice@example.com	I adapt quickly.	2	2	2	6
bob@example.com	I feel upset and avoid change.	0	1	1	2

 Pro Tips

• Always use TRIM() to clean extra spaces.

• Copy-paste full answers directly from the Google form to the scoring map 

• This is to avoid punctuation mismatches (e.g., periods, commas).

• Use ARRAYFORMULA for automation if you're comfortable.

What is ARRAYFORMULA?

ARRAYFORMULA allows you to apply a formula to an entire column of data without dragging it down manually.

Example:

=ARRAYFORMULA(IF(C2:C="", "", IFERROR(VLOOKUP(C2:C, FILTER('Scoring Map'!B:C, 'Scoring Map'!A:A="Q1"), 2, FALSE), 0)))

• This fills Q1_Score for all rows in one step.

• No dragging is needed.

• Make sure there’s nothing below the formula cell or it will show a conflict.

You can do the same for Q2 and Q3.

This scoring method gives you full control and transparency, especially useful for interviews, self-assessments, or behavioral screening forms. 

The Intrinsic Joys of Teaching Needlework

Needlework and its myriad branches are subjects that have never failed to enthral me or to awaken the teacher, the designer and the explorer in me. I can list at least four reasons why despite being at it for well over 20 years, I still find teaching the craft captivating.

1. I enjoy the various facets of Needlework- Needlework is a very broad term given to an amazing variety of things you can design with just a bit of yarn, implements like the humble sewing needle, a pair of knitting needles, a crochet hook, an exotic tatting shuttle etc., and your fingers. Although needlework is basically categorized into freestyle embroidery, counted thread embroidery, and lace making, each of these labels have under their umbrella, an astonishing number of constantly expanding styles. For instance, crochet, knitting and tatting fall under lace making; however, within the  category of tatting fall distinct styles like Cluny tatting, beaded tatting, split ring tatting, needle tatting etc. Exploring all of these, and developing methods to help my students understand the nuances mastering them would require, are at the same time both a challenging and an extremely enjoyable experience.
2. I enjoy being part of my students' projects- Why would I enjoy being a part of other people's projects? As I've mentioned earlier, needlework is a very broad term, and if I were to try out every project that captivates my      imagination, I would have very little time to expand my horizons and learn new techniques. Therefore, I love to participate in my students projects from start to finish and to help them explore multiple ways to use a design; furthermore, the part I enjoy the most is exploring with them how different a specific design would look when worked using different design elements, classes of stitches or styles of embroidery. These exercises satiate the designer in me.
3. The more I teach the more I learn- Upon reflection, I realize that  teaching needlework has forced me to look at things from other people's point of view; mostly because, I've never had some of the doubts my students raise when I was learning the techniques myself.  In such cases, I have to first figure out why the student had the doubt in the first place, find a solution, and then add it to my repertoire of teaching techniques. In one instance, since I am right-handed, I found it difficult to address doubts raised by my left-handed students until one of them pointed out that all I had to do was use an image editing software to flip the images illustrating the instructions so I had a mirror image; I'd now have images suited for a left-handed student. At times, a student would illustrate an easier technique she had learned from her mom to work a stitch; even though I am the teacher, teaching needlework is never a one way street, for my students constantly motivate me to expand my teaching skills and to explore the use of technology in teaching needlework. 
4. Teaching needlework akin to meditation- Finally, teaching needlework and meditating have the same effect on me; in particular, it's a great stress buster because when I am teaching needlework I am so into it that for brief periods of time, everything else ceases to exist. 

The joy I get out of teaching my needlework classes is an almost addictive transcendental experience.