Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

# How can I simplify this nested IF statement?

Topic Labels: Formulas
354 7
cancel
Showing results for
Did you mean:
8 - Airtable Astronomer

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?

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’
))))))

7 Replies 7
16 - Uranus

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'
)
``````
8 - Airtable Astronomer

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?

16 - Uranus

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

``````   BLANK(), '',
``````
8 - Airtable Astronomer

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.

16 - Uranus

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])`

8 - Airtable Astronomer

This one works:

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

8 - Airtable Astronomer

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!