Google Sheets formula for “if contains” for grading multiple choice quiz

Question

I want to use a Google Sheets formula to correct a multiple choice survey that I built using Squarespace. The results get dumped into Google sheets.

For example, if the respondent selects choice:

• A. then they would get 0 points
• B. then they would get 1 point
• C. then they would get 2 points

I tried to string several Regexmatch statements together such as the below but it won't work for more than one regexmatch at a time:

=if(regexmatch(A2, "A."), 0,) (regexmatch(A2, "B."), 1,) (regexmatch(A2, "C."), 2,)

Any advice would be appreciated.

Answer 1

You can try =switch :

=SWITCH(A1:A10,"A",0,"B",1,"C",2)

Result :

---

Same result with regexmatch :

=ArrayFormula(
if(REGEXMATCH(A1:A10,"A"),0,
if(REGEXMATCH(A1:A10,"B"),1,
if(REGEXMATCH(A1:A10,"C"),2,"no match")))
)