Re: Nested IF(SEARCH()) Formulas

Solved
1325 1
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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.

1 Solution

Accepted Solutions
Airtable Employee

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("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"
)))))))))))))``````
5 Replies 5
10 - Mercury

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"
)
)``````
Airtable Employee

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("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"
)))))))))))))``````
5 - Automation Enthusiast

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!!

5 - Automation Enthusiast

Hello Zollie,

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

Airtable Employee

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