Nested IF(SEARCH()) Formulas

[Robin_POLGUER]

Hey there,

In order to better manage my emailing campaigns and some of the auto replies I’m receiving I integrated my sending box with Airtable. To ease the cleaning I would like to add within a column a specific code based on the corpus of the auto reply. Here is an example of the formula I came up with:

IF(SEARCH(“yyyy”,Corpus),“Click to validate”,IF(SEARCH(“XXXX”,Corpus),“Nothing”,IF(SEARCH(“aaaa”,Corpus),“Nothing”,IF(SEARCH(“bbb”,Corpus),“Nothing”,IF(SEARCH(“ccc”,Corpus),“Nothing”,IF(SEARCH(“ddd”,Corpus),“Unsubscribe & Check for address”,IF(SEARCH(“eeee”,Corpus),“Unsubscribe & Check for address”,IF(SEARCH(“fff”,Corpus),“Unsubscribe”,IF(SEARCH(“gggg”,Corpus),“Unsubscribe”,IF(SEARCH(“hhhh”,Corpus),“Unsubscribe”,IF(SEARCH(“iii”,Corpus),“Unsubscribe”,IF(SEARCH(“jjj”,Corpus),“Unsubscribe”,IF(SEARCH(“kkkk”,Corpus),“Click to validate”,)))))))))))))

but it doesn’t seem to be working. Do you guys have any clue why?

I may have to add new criteria moving forward.

Thanks a lot in advance for your kind help!

[Zollie]

That’s quite the formula. I’d recommend breaking it up into chunks so that you can see if parts of it are working in isolation (particularly useful for type related errors, such as using a string when an integer is required). You also might want to do some code indentation while working on it in a separate text editor so it’s easier to figure out what’s going on (where typos are, such as a missing comma). For example:

IF(
    Logic,
    "if true",
    IF(
        Logic,
        "if true",
        "if false"
    )
)

[Jason · Accepted solution]

Hi @Robin_POLGUER,

Looks like there are two minor mistakes in your formula:

1. The added comma after your very last ELSE condition (“Click to validate”,). Since it’s the last conditional statement in your formula, you need to end with only a closed parenthesis rather than with a comma.
2. The use of “curly quotes” - Airtable can only read straight quotes.

Here’s your formula with the adjustments for the two items above. And, big +1 for @Zollie’s suggestion to break your formula into chunks to troubleshoot and indent when helpful.

IF(SEARCH("yyyy",Corpus),"Click to validate",
IF(SEARCH("XXXX",Corpus),"Nothing",
IF(SEARCH("aaaa",Corpus),"Nothing",
IF(SEARCH("bbb",Corpus),"Nothing",
IF(SEARCH("ccc",Corpus),"Nothing",
IF(SEARCH("ddd",Corpus),"Unsubscribe & Check for address",
IF(SEARCH("eeee",Corpus),"Unsubscribe & Check for address",
IF(SEARCH("fff",Corpus),"Unsubscribe",
IF(SEARCH("gggg",Corpus),"Unsubscribe",
IF(SEARCH("hhhh",Corpus),"Unsubscribe",
IF(SEARCH("iii",Corpus),"Unsubscribe",
IF(SEARCH("jjj",Corpus),"Unsubscribe",
IF(SEARCH("kkkk",Corpus),"Click to validate"
)))))))))))))

[Robin_POLGUER]

Hello Jason,

Thanks a million! It’s working perfectly! I’m definitely going to take you guys advice about breaking my formulas into chunk next time!

Once again thank you both for your help!!

[Robin_POLGUER]

Hello Zollie,

Thanks for the tips! Will not miss to use it next time :slightly_smiling_face:

[Jason]

Happy to help! For future reference you can also see our growing list of support articles around formulas: https://support.airtable.com/hc/en-us/categories/360003075854