worksheet function – Excel formula/solution for grading multiple choice questions with multiple answers

I have an Excel sheet where I input student test answers like so:

  1. A
  2. B
  3. A,B
  4. C,D

and compare these answers to the correct answers:

  1. A
  2. A
  3. B
  4. C,D,E

I know how to use an IF function to output ‘CORRECT’ in a cell if the student answer is correct (e.g. answer 1) and otherwise spit out ‘INCORRECT’.

What I can’t seem to do is find a formula which will spit out ‘CORRECT’ if correct (1); ‘INCORRECT’ if wrong (2); ‘MISTAKE’ if the student got one of the answers correct, but at the same time answered another choice incorrectly (3); and ‘MISSED’ if the student answered correctly but missed an answer (4).

Is there any way to do this? I’ve tried using wildcards and countifs but it’s beyond my Excel level.

Thanks in advance for any help!