Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Recreate If statement from excel

Topic Labels: Community Formulas
366 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Nawaf
4 - Data Explorer
4 - Data Explorer

Hello everyone

I'm Nawaf

I have a long nested if statement that i used to use in excel but I can't figure out how to use it in airtable 

the idea is i have 3 columns first ( permit status ) second is ( remark ) when the conditions meet, I want the third column ( action ) to show as "action" or "no action" or "no action closed"

(permit states) has only two terms closed or valid

(remark) is different i want to find the word "renewed" or "to be" or "re-issue" or "on hold" "redesign"

this is what i used to use in excel 

=IF(AND([@[Permit Status]]="Valid",[@Remark]="No need for renewal"),"no action",IF(AND([@[Permit Status]]="closed",[@Remark]="No need for renewal"),"no action closed",IF(AND([@[Permit Status]]="closed",[@Remark]=""),"need action",IF(AND([@[Permit Status]]="Valid",[@Remark]=""),"no action",IF(AND([@[Permit Status]]="Valid",[@Remark]="Renwd"),"no action",IF(AND([@[Permit Status]]="Valid",[@Remark]="Re-issued"),"no action",IF(AND(ISNUMBER(SEARCH("to be",[@Remark])),[@[Permit Status]]="valid"),"in progress",IF(AND(ISNUMBER(SEARCH("to be",[@Remark])),[@[Permit Status]]="closed"),"in progress",IF(AND(ISNUMBER(SEARCH("on hold",[@Remark])),[@[Permit Status]]="closed"),"no action",IF(AND([@[Permit Status]]="closed",[@Remark]="renewd"),"no action",IF(AND([@[Permit Status]]="closed",[@Remark]="Re-issued"),"no action",IF(AND([@[Permit Status]]="closed",[@Remark]="redesign"),"no action"))))))))))))

I would apricate any help 

Thank you

1 Reply 1

Airtable formulas use curly brackets to indicate whether we're referencing a field name.  With reference to your formula, if we're referencing a field called "[@Remark]", we would use "{[@Remark]}", and so you'd need to update your formula accordingly

I would also recommend formatting the formula so that you can view stuff easily

I've combined both of the changes I mentioned above below and you'd just need to make the changes accordingly:

IF(
  AND(
    {[@[Permit Status]]}="Valid",
    {[@Remark]}="No need for renewal"
  ),
  "no action",
  IF(
    AND(
      {[@[Permit Status]]}="closed",
      {[@Remark]}="No need for renewal"
    ),
    "no action closed"
  )
)

Screenshot 2024-06-11 at 10.53.23 PM.png
Link to base