If contain write

Hi community,
I need your help.
There is something i don’t understand with the formula bellow :

IF(
    SEARCH("Jungle", {Order Line Items}),
    "Jungle",
    IF(
        SEARCH("Baby Plantes", {Order Line Items}),
        "Baby plante",
        IF(
SEARCH("Pets Friendly", {Order Line Items}),
            "Pet's Friendly"
        )
    )
)

I want to write a 4th condition but it never work.
What is the importance of the space between the condition and the “)” ? How can i built easly this type of formula ?

Thank’s a lot,

Matthieu

You can have unlimited white space (new lines and spaces) between
parameters of a function. The amount of space between parameters has
no impact on the result of the formula. The purpose of the white space is to
make the formula more human readable.

Here is your formula rewritten with my style of white space:

IF(
    SEARCH("Jungle", {Order Line Items}),
    "Jungle",
IF(
    SEARCH("Baby Plantes", {Order Line Items}),
    "Baby plante",
IF(
    SEARCH("Pets Friendly", {Order Line Items}),
    "Pet's Friendly"
)))

You can easily add more nested conditions, keeping in mind the following rules:

The number of closing parenthesis at the end must match the number of IFs.

All lines end with a comma except the following situations:

  • the line end with an open parenthesis
  • the following line begins with a closing parenthesis (ignoring white space)
1 Like

Than’ks a lot for the quick answer !
The problem come from the comma.

Thank’s,

Matthieu

I tested a variation of your formula. For a few minutes I also had trouble going beyond three IF statements — but in retrospect I’m not sure why. I was almost certainly making a syntax mistake. Kuovonne mentioned that spaces make no difference. Mostly true, but your formula will break if you have a space between the function name and the opening parenthesis. This seems to be a quirk of Airtable. In other words

IF( 2>1, "True", "False )

will work, but

IF ( 2>1, "True", "False" )

will not work because of the space between IF and the opening (.

And just to confirm, this formula with 5 nested IF statements worked for me (and I have written much longer ones in the past):

IF( SEARCH( 'Al', Notes ), 'Al',
IF( SEARCH( 'Bob', Notes), 'Bob',
IF( SEARCH( 'Chris', Notes), 'Chris',
IF( SEARCH( 'Dave', Notes), 'Dave',
IF( SEARCH( 'Ellen', Notes), 'Ellen',
'ERROR'

)
)
)
)
)

Technically, I said that spaces between parameters do not make a difference.
Spaces between a function name and its opening parenthesis is not a space between parameters. As the original formula did not have a problem with spaces, I didn’t seen the need to point out that situation.

Matthieu said that the problem came from a comma. Since there was not a problem with commas in the original formula, he must mean either a missing or extra comma when he was adding the fourth condition. This is why I format my nested IFs the way I do. It makes it much easier to identify missing or extra commas (according to the rules that I included in my post.)

1 Like

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.