Issue with nested "IF"

Kathy_Weston · ‎Jan 17, 2020

I know this has been discussed before and I have read the questions and comments. Unfortunately I am stuck.
I have a multi select field that I am trying to extract information from.
The formula works fine in Excel
=IF(O2=“Individual”,15,IF(O2=“Family”,20,IF(O2=“Social”,10,IF(O2=“No Cost”,0,“0”))))

I have tried the following and none work:

=IF({Membership Test 2}=“Individual”,15,IF{Membership Test 2}=“Family”,20,IF{Membership Test 2}=“Social”,10,IF{Membership Test 2}="No Cost”,0,”0”))))

=IF({Membership Test 2}={Individual},15,IF{Membership Test 2}={Family”,20,IF{Membership Test 2}={Social},10,IF{Membership Test 2}={No Cost},0,”0”))))

=IF({Membership Test 2}=Individual,’15’,IF{Membership Test 2}=Family,’20’,IF{Membership Test 2}=Social,’10’,IF{Membership Test 2}=No Cost,’0’,0))))

Can anyone help? Thanks

JonathanBowen · ‎Jan 18, 2020

Hi @Kathy_Weston - your original formula works fine for me (if you change the formatted quotes to not formatted):

IF(
  O2='Individual',
  15,
  IF(
    O2='Family',
    20,
    IF(
      O2='Social',
      10,
      IF(
        O2='No Cost',
        0,
        0
      )
    )
  )
)

Maybe where you’ve gone wrong with this is including the first “=” - unlike Excel, Airtable doesn’t need you to prefix a formula with “=”.

As an aside, I often use a SWITCH formula for these cases - a little bit easier to manage:

SWITCH(
  O2,
  'Individual', 15,
  'Family', 20,
  'Social', 10,
  'No Cost', 0
)

JB

Kathy_Weston · ‎Jan 18, 2020

Hi Jonathan,
Thank you very much for your help. I love the “Switch” formula.
I am use to working in FileMaker Pro and Airtable doesn’t work quite the same.
Again, Thanks!