# Re: Formula using lookup & rollup dates returns only blank cells?

Solved
333 0
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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!

1 Solution

Accepted Solutions
18 - Pluto

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”.

3 Replies 3
18 - Pluto

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”.

18 - Pluto

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

\

5 - Automation Enthusiast

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!