IF Statement Headache - Cannot seem to solve

[Rian_Carr]

I am trying to write a string of IF statements…so the logic is this:
If user selects "Country’ as “North Vietnam” and/If the ‘Ultimate Destination’ is “Long Beach”, then the result should be the date column{North Vietnam to LAX}. This works. But…

The problem is it won’t let me keep adding to the IF string. for example below: it won’t recognize the "China’ Query. I am really struggling with this one…any help would be so much appreciated.

IF({Country}=“North Vietnam”,IF({Ultimate Destination}=“Long Beach”,{North Vietnam to LAX},IF({Country}=“South Vietnam”,IF({Ultimate Destination}=“Long Beach”,{South Vietnam to LAX} IF({Country}=“China”,IF({Ultimate Destination}=“Long Beach”,{China to LAX},IF({Country}="India ",IF({Ultimate Destination}=“Long Beach”,{India to LAX}))))

This formula works, but I cannot add onto it:
IF({Country}=“North Vietnam”,IF({Ultimate Destination}=“Long Beach”,{North Vietnam to LAX}))

[Jason]

Hi @Rian_Carr,

I’d recommend structuring your formula with IF(AND( statements. I wasn’t sure if the results of your functions (e.g. {North Vietnam to LAX}) are intended to be field names, or just a text result, but here’s a formula to get you started:

IF(
   AND(
      {Country}="North Vietnam",
      {Ultimate Destination}="Long Beach"
   ),
   {North Vietnam to LAX},
   
IF(
   AND(
      {Country}="South Vietnam",
      {Ultimate Destination}="Long Beach" 
   ),
   {South Vietnam to LAX},

IF(
   AND(
      {Country}="CHINA",
      {Ultimate Destination}="Long Beach" 
   ),
   {China to LAX},

IF(
   AND(
      {Country}="India",
      {Ultimate Destination}="Long Beach" 
   ),
   {India to LAX}
)
)
)
)

Let me know if that helps!

[Justin_Barrett]

Here’s another way to think about it. In your example, all of the destinations were “Long Beach.” You could start off looking for the destination, then pick the route to that destination using SWITCH():

IF(
    {Ultimate Destination}="Long Beach",
    SWITCH(
        {Country},
        "North Vietnam", {North Vietnam to LAX},
        "South Vietnam", {South Vietnam to LAX},
        "China", {China to LAX},
        "India", {India to LAX}
    )
)

Or you could use nested SWITCH() functions:

SWITCH(
    {Ultimate Destination},
    "Long Beach", SWITCH(
        {Country},
        "North Vietnam", {North Vietnam to LAX},
        "South Vietnam", {South Vietnam to LAX},
        "China", {China to LAX},
        "India", {India to LAX}
    )
)

[Rian_Carr]

This is super helpful! I got the first statement to work, but if I paste this whole thread into Airtable it gives me an error. What am I doing wrong? Should I be able to paste this directly into AT and it work, hopefully? Is it because of the spaces involved? do spaces matter when building these formulas? and the {North Vietnam to LAX} is a field we have. So it calls that field. this is EXACTLY what I want to do but I seem to get and error after it reads the first IF/AND statements.

the help is so so appreciated here. really. Thank you.

For instance:
Here I added a country and ultimate destination statement.
IF(
AND(
{Country}=“North Vietnam”,
{Ultimate Destination}=“Long Beach”
),
{North Vietnam to LAX},

IF(
AND(
{Country}=“South Vietnam”,
{Ultimate Destination}=“Long Beach”
),
{South Vietnam to LAX},

IF(
AND(
{Country}=“CHINA”,
{Ultimate Destination}=“Long Beach”
),
{China to LAX},

IF(
AND(
{Country}=“India”,
{Ultimate Destination}=“New York”
),
{Indonesia to NY}
IF(
AND(
{Country}=“North Vietnam”,
{Ultimate Destination}=“New York” ),
{North Vietnam to NY},
)
)
)
) )

[Rian_Carr]

Justin - many thanks!
In the configuration I’m working with, the {Ultimate Destination} can be multiple places. It might be New York, Vancouver etc. I am going to try this as well. If I were to build off this, would add another switch statement and where? I am so thankful for this community of help…I’d be stuck.
Thanks!
Rian

[Rian_Carr]

SOLVED!!! Thank you so much!

[Justin_Barrett · Accepted solution]

I hope @Jason doesn’t mind me jumping in, but I spotted the problem with the modification you made to his formula. You left out a comma after {Indonesia to NY}, before the final IF() function.

Rian_Carr:

Is it because of the spaces involved? do spaces matter when building these formulas?

Spaces don’t matter. I like building complex formulas in a code editor (BBEdit in my case) because it allows me to spread things out for better readability, and I know the formula will still work in Airtable.

Rian_Carr:

If I were to build off this, would add another switch statement and where?

Because you have multiple options for {Ultimate Destination}, I suggest going with the second option, which uses nested SWITCH() functions, like so:

SWITCH(
    {Ultimate Destination},
    "Long Beach", SWITCH(
        {Country},
        "North Vietnam", {North Vietnam to LAX},
        "South Vietnam", {South Vietnam to LAX},
        "China", {China to LAX},
        "India", {India to LAX}
    ),
    "New York", SWITCH(
        {Country},
        "North Vietnam", {North Vietnam to NY},
        "South Vietnam", {South Vietnam to NY},
        "China", {China to NY},
        "India", {India to NY}
    ),
    ...add more SWITCH() functions as needed...
)

[Jason]

Justin_Barrett:

I hope @Jason doesn’t mind me jumping in, but I spotted the problem with the modification you made to his formula.

Not al all, thanks @Justin_Barrett!