- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 05, 2021 02:26 PM
Hello!
Hoping I could get some advice on a formula field that accepts my formula but the cells won’t return any data (not even an #error message). Maybe it’s a bug…? Since I’m dealing with multiple formula fields, lookups and a Rollup result that is being fed back to the original table, I’m thinking it’s an issue with Airtable recognizing the data…Any workarounds or syntax advice?
Problem formula is in table 2:
IF({Legal Wraps After}=‘Episode’, {Episode Legal Wrap Date}, IF({Legal Wraps After}=“Season”, {Season Legal Wrap Date}, BLANK()))
Table 1: SEASONS
- {Legal Wraps After} - Single Select Field (episode / season)
- {Last Shoot Date} - Rollup Field (MAX {Shoot Date} of all Episodes in a Season)
Table 2: EPISODES
- {Shoot Date} - Date Field
- {Episode Legal Wrap Date} - Formula Field: ({Shoot Date} + 7 days)
- {Season Legal Wrap Date} -Lookup Field: (Lookup the {Last Shoot Date} Rollup Field)
Any help here would be much appreciated! Thanks!
Solved! Go to Solution.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 05, 2021 09:15 PM
You say that the formula is in table 2—the [Episodes]
table—but the first field that the formula references ({Legal Wraps After}
) is in the first table, [Seasons]
. Are you looking up the value of {Legal Wraps After}
based on a link so the formula can use it?
The only syntax advice I can offer doesn’t directly address this problem, but does make the structure a little more streamlined. Instead of using nested IF()
functions, I suggest using the SWITCH()
function. The transformation would look like this:
SWITCH(
{Legal Wraps After},
"Episode", {Episode Legal Wrap Date},
"Season", {Season Legal Wrap Date}
)
Whether you use IF()
or SWITCH()
, you can omit the reference to BLANK()
at the end. The last argument in both functions is optional, and will default to the proper blank-equivalent value based on the type returned by the other options.
Back to your issue, you might check the options that you added to your {Legal Wraps After}
single select field. Specifically, look for spaces at the ends of those words. If the entry is "Episode " (notice the space), then it won’t match when compared against “Episode”.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 05, 2021 09:15 PM
You say that the formula is in table 2—the [Episodes]
table—but the first field that the formula references ({Legal Wraps After}
) is in the first table, [Seasons]
. Are you looking up the value of {Legal Wraps After}
based on a link so the formula can use it?
The only syntax advice I can offer doesn’t directly address this problem, but does make the structure a little more streamlined. Instead of using nested IF()
functions, I suggest using the SWITCH()
function. The transformation would look like this:
SWITCH(
{Legal Wraps After},
"Episode", {Episode Legal Wrap Date},
"Season", {Season Legal Wrap Date}
)
Whether you use IF()
or SWITCH()
, you can omit the reference to BLANK()
at the end. The last argument in both functions is optional, and will default to the proper blank-equivalent value based on the type returned by the other options.
Back to your issue, you might check the options that you added to your {Legal Wraps After}
single select field. Specifically, look for spaces at the ends of those words. If the entry is "Episode " (notice the space), then it won’t match when compared against “Episode”.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 06, 2021 08:10 AM
It looks like your formula in table 2 is referencing a lookup in table 1. A lookup returns an array, not a string.
Use @Justin_Barrett’s formula with a slight addition of forcing the lookup into a string.
SWITCH(
{Legal Wraps After} & "",
"Episode", {Episode Legal Wrap Date},
"Season", {Season Legal Wrap Date}
)
\
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 08, 2021 11:59 AM
Thank you @Justin_Barrett and @kuovonne for suggestions! It was totally that I had an extra space in both "Episode " and "Season " in Table 1. Everything is working fine now!
