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.