IF with HAS and Multiple Choice dropdown.

dhall

This is a complicated one so bear with me.

We have an form users fill out and one of the questions on the form is multiple choice where they can select more than one answer. There are 3 correct answers to this and fourth option that is incorrect. We've been asked to give to give them "10 points" if they get all 3 correct, "5 points" if they get 2 out of the correct 3, and "0 points" if they select the wrong one or only choose 1 correct one.

I tried this with HAS(INDEX() but the Criteria only looks for one. Here's what I came up with that worked for one correct answer:

=IF(HAS(INDEX({L2I - H}, MATCH(Name@row, {L2I - A}, 0)), "Marketing"), 10, 0)

The INDEX(MATCH()) is to find the specific cell based on the column (L2I - H) and the row based on their name (L2I - A). In the HAS() function, that INDEX(MATCH()) points the HAS() to the right cell.

"Marketing" is the criteria I am searching for. However, the other two correct choices are "Setup" and "Identification Card". The wrong choice is "Equipment".

If they select "Marketing", "Setup", and "Identification Card" in that multiple choice on the form, they would get 10 points. If they select "Setup" and "Identification Card" they would get 5 points. If they select "Marketing", "Setup" and "Equipment, 0 points. If they just selected "Marketing", 0 points, etc. etc.

Let me know if I can provide any other details. For the time being, to assign 10 points I have it set as this formula:

=IF(INDEX({L2I - H}, MATCH(Name@row, {L2I - A}, 0)) = {L2I - H1}, 10, 0)

Essentially this works out to, "If their cell matches the answer cell (L2I - H1), 10 points, otherwise 0 points."

Thanks in advance!

dhall

So an update to what has been on my mind regarding this solution. 4 Helper columns, one per "choice" and then a formula to review the helper columns with IF statements.

I don't know if that's a more complicated solution, but I'd like to hear what others are doing in this type of situation before I begin crafting that formula up.