**The solution**

Lets represent the answer in binary like this, with bit order EDCBA i.e.

```
01010
```

means the answer is

```
B,D
```

To convert the text answer (student or answer sheet ) we use

```
=ISNUMBER(SEARCH("A", A1))*2^0 + ISNUMBER(SEARCH("B", A1))*2^1 + ISNUMBER(SEARCH("C", A1))*2^2 + ISNUMBER(SEARCH("D", A1))*2^3 + ISNUMBER(SEARCH("E", A1))*2^4
```

Now to compare. Lets say we have C1 the correct answer and D1 the student answer, both in "bit-wise" form using above formula. Then

```
=IF(C1 = D1, "Correct", IF(BITAND(BITXOR(C1,D1),C1)=C1, "Incorrect", IF(BITAND(BITXOR(C1,D1),C1)=BITXOR(C1,D1), "Missing", "Mistake")))
```

**The explanation**

I could try and explain it, but I would have to kill you and then myself. Maybe I can try... It helps to think of detecting the "mistake" as the difficult one, so leave it for the last "catch all" case of the if. Consider skipping this in favour of the image below:

```
X XOR Y is a list of differences between lists X and Y
X AND Z = X means the list Z must at least contain everything in list X
X AND Z = Z means the list X must at least contain everything in list Z
Now lets say X is the list of correct answers (CA), Y is list of the student's answers (SA). Then:
Z = X XOR Y is a list of differences between CA and SA
If Z = 0 then the list is empty and CA = SA i.e. "Correct", else if
X AND Z = X
then the list of differences must contain at least everything in list of correct answers (i.e. no correct answers = "incorrect"), else if
X AND Z = Z
then the list of correct answers must contain at least everything in the list of differences (i.e. no wrong answers = "missing", one or more correct), else
NOT(all of the above)
then one or more correct answer and one or more incorrect answer = "Mistake".
```

**TL;DR**

Its actually quite easy if you draw it (insight after sleeping!):