How can I simplify this nested IF statement?

[Markus_Wernig]

I know the way I am doing this is unnecessarily complicated and even though it works, I’d be interested in learning how to do the same thing more efficiently.

I have a formula field that looks at the content of another field ({WRITER 2: SOCIETY} and is supposed to fill in a corresponding number.

{WRITER 2: SOCIETY} can contain either the names ASCAP, SESAC, BMI, or any other name.
ASCAP, SESAC, and BMI all have corresponding numbers (with leading zeros) that should be filled in if the statement is true.

• ASCAP#: 00701163494
• SESAC#: 00850346936
• BMI#: 00794154022

If {WRITER 2: SOCIETY} contains any other name, fill in the BMI#: 00794154022

This is what I am doing. How can I simplify this formula?
Thank you in advance!!!

IF(
{WRITER 2: SOCIETY}=0,
‘’,
IF(
{WRITER 2: SOCIETY}=‘ASCAP’,
‘00701163494’,
IF(
{WRITER 2: SOCIETY}=‘SESAC’,
‘00850346936’,
IF(
{WRITER 2: SOCIETY}!=‘ASCAP’,
‘00794154022’,
IF(
{WRITER 2: SOCIETY}!=‘BMI’,
‘00794154022’,
IF(
{WRITER 2: SOCIETY}!=‘SESAC’,
‘00794154022’,
‘00794154022’
))))))

[Kamille_Parks]

Seems like you could just use a SWITCH() statement since you’re comparing the same item to multiple static values:

SWITCH(
   {WRITER 2: SOCIETY},
   0, '',
   'ASCAP', '00701163494',
   'SESAC', '00850346936',
   'BMI', '00794154022',
   '00794154022'
)

[Markus_Wernig]

Thank you so much, @Kamille_Parks !

The much simpler formula works for the most part.
What doesn’t work is when {WRITER 2: SOCIETY} is empty.
For that case '‘00794154022’ gets filled in as well.
How can we avoid that?

[Kamille_Parks]

Add another line before the final one (the default value for ‘00794154022’:

   BLANK(), '',

[Markus_Wernig]

SWITCH(
{WRITER 2: SOCIETY},
0, ‘’,
‘ASCAP’, ‘00701163494’,
‘SESAC’, ‘00850346936’,
‘BMI’, ‘00794154022’,
BLANK(), ‘’,
‘00794154022’
)

When I add BLANK(), ‘’, Airtable says it’s an invalid formula code and doesn’t allow me to save it.

[Kamille_Parks]

Then either replace BLANK() with '' or remove the BLANK() line and wrap the whole SWITCH() in an IF() statement: IF({WRITER 2: SOCIETY}, [insert switch])

[Markus_Wernig]

This one works:

IF({WRITER 2: SOCIETY}=BLANK(),’’,
SWITCH(
{WRITER 2: SOCIETY},
‘ASCAP’, ‘00701163494’,
‘SESAC’, ‘00850346936’,
‘BMI’, ‘00794154022’,
‘00794154022’
))

[Markus_Wernig]

I see what you’re saying. I don’t even need the BLANK().

Yes, this one works as well:

IF({WRITER 2: SOCIETY},
SWITCH(
{WRITER 2: SOCIETY},
‘ASCAP’, ‘00701163494’,
‘SESAC’, ‘00850346936’,
‘BMI’, ‘00794154022’,
‘00794154022’
))

Thank you!