IF MULTIPLE SELECT - single array cell

[Gio]

Hello airtablers,

I have a field {RESPONSE} with 5 possible choices A,B,C,D,E

I need to be able to give a score based on each response.

So if response is A OR B score is 4
if response is C OR D score is 1
if response is E score is 3

This would be easy to solve with a switch function but the cell with responses returns all the responses concatenated in a single array (A;B;C;D;E).

Is there a way to score the responses?

Thanks a lot

[ScottWorld · Accepted solution]

You can use the SEARCH function within a nested IF statement like this:

IF(
SEARCH("A",Response),4,
IF(
SEARCH("B",Response),4,
IF(
SEARCH("C",Response),1,
IF(
SEARCH("D",Response),1,
IF(
SEARCH("E",Response),3
)))))

Hope this helps! If this answers your question, could you please mark this comment as the solution to your question? This will help other people who have a similar question. :slightly_smiling_face: