Nested IF() with FIND() and BLANK()

Hi everyone!

Thank you in advance for taking a look at this. I have a formula that I think is REALLY CLOSE but keeps giving me an error. The field I’m having the formula search contains a series of state abbreviations, so MI may or may not be present in the string, and I want to differentiate between MI not being present (but other states being present) and the cell being blank. The formula should work a little something like this: IF (MI is present, then “SLP”), IF (MI is not present, then “Applying”), IF(BLANK(), “Candidate”).

Here’s what I have so far. Each half works by itself, but when I put them together they always give me an error.

IF({SLPs Licenses (from SLP Onboarding Link)}=BLANK(), “Candidate”,

IF(

FIND(“MI”,{SLPs Licenses (from SLP Onboarding Link)}),“SLP”, "Applying”))

Welcome to the Airtable community!

I recommend writing this as a multi-line formula, using a text editor outside of Airtable.

IF(
  {SLPs Licenses (from SLP Onboarding Link)},
  IF(
    FIND( "MI", {SLPs Licenses (from SLP Onboarding Link)} ),
    "SLP",
    "Applying"
  ),
  "Candidate"
)

1 Like

Thank you so much! That’s brilliant! Now to figure out why that worked. :thinking:

It is nested IF statements. The outter IF statement checks if there is a value in your field. If your field has a value, the formula evaluates the inner IF statement. If your field does not have a value, the formula returns “Candidate”.

The inner IF statement checks if the string “MI” exists in the field. If the string exists, it returns “SLP”, and if the string does not exist, it returns “Applying”.

Each IF statement has three parts (parameters): the condition, the “true” result, and the “false” result. In this case, the “true” result of the outter IF is the result of the nested IF.

You can see that each IF has three parts by looking at the indent level of each line. This is easiest to see in the inner IF statement. The inner IF starts on line 3 and ends on line 7 (closing parenthesis), with one parameter per line on lines 4, 5, and 6.

The outer IF also has three parameters. The outer IF starts on line 1 and ends on line 9 (closing parenthesis). The first parameter is on line 2. The second parameter is the nested IF on lines 3-7, and the third parameter is on line 8.

1 Like

Another thing worth pointing out here is how BLANK() isn’t really all that useful in Airtable, notice how IF(FieldName,“Something”,“Nothing”) will produce the same results as the much wordier "IF(FieldName=BLANK(),“Nothing”, “Something”).

That’s because it’s basically saying IF(FieldName exists,do this, otherwise do that)

Or just leave the response empty “” to condition a blank of your own.

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