Understanding IF and SWITCH Formulas in Airtable | GAP Consulting

Using formulas is a critical element of building a solid database, but they can be intimidating, especially if you're not accustomed to writing formulas in Excel or similar tools.

One of the most commonly used formulas is the IF formula. This formula checks a logical value that you've assigned. If the logical value is 'true' then the formula spits out one output - if it's 'not true' then a second output is used. In its simplest form, IF statements are straightforward and simple to understand.

However, it's common that two outputs aren't sufficient. For these cases, we need to rely on more advanced options. One such option is known as a Nested IF formula - this formula combines one or more IF formulas, built within one another. For example, if logic(1) is true, then output(1), otherwise if logic(2) is true, then output(2), otherwise output(3).

As you can imagine, Nested IF formulas can get quite complex. Sometimes they are the best and only way to solve a problem. But, other times it may be easier to use a SWITCH formula. The SWITCH formula checks for multiple possibilities and derives its output based on what it finds. This is a much simpler formula to write than a complicated Nested IF statement!

_________________________________________

Learn more at these links: 🔗 👇

Join us for our next live training!

Take our free Airtable Crash Course!

Getting Stuck? See how we can help!

Get our Airtable Field Type download!

_________________________________________

Table of Contents: 📖 👇

00:00 - Intro
00:49 - Setting the Stage
02:35 - Simple IF formula for a Checkbox
06:34 - Simple IF formula for a Status (Single Select)
08:05 - Using a Nested IF Formula to combine the IFs
11:06 - Using a SWITCH formula
15:21 - Get More Help!

_________________________________________

Software Resources (may contain affiliate links): 💻 👇

_________________________________________

#GAPConsulting #GarethPronovost #Airtable

[ooooooooa]

Best Airtable channel so far! Thank you Gareth

[JonathanShyman]

Yes! More formula videos PLEASE! Thanks!

[angelprado]

Excellent, I was looking for a Case Function or something like that and yo help me a lot, thanks

[williamho]

Thank you Boss! Always looking forward to your new video!!

[shaunparfett]

Thanks again Gareth for an informative video.

[itseveryonelese]

Very cool! Loving the switch formula!!!!

[wilmermoreno]

Hello, I got a question, in this case, for my status I have different inputs, but I want the switch function to output and specific value for a set of different inputs:

EX: I want for the outcome to be cancelled If the deal status is either cancelled, disqualified, expired and so on.

I would appreciate the help here

[sybdeclark]

Thank you so much for your videos. I am new to Airtable and they are very, very valuable to me.
This one inspired me a question. In your example, could you use an if statement (or some other formula) to get the checkbox ticked automatically when the status is set to "done"? Generally speaking, can the output of an if statement be something else than text, in particular some kind of action (like click checkbox in this example)? Thanks in advance!

[wisdomofaclown]

Very new to AirTable. Loving it so far and really enjoying your videos. I’ve already learned a lot.
One thing I’ve been trying to figure out. My wife is starting online reselling business. Some items are things we buy and some are things we sell for others and pay them a % of the sales. I have a column for the % going to the supplier and one for our profit after all other amounts are deducted if the item is supplied by someone else. Is there a way to do an if statement or some other formula that says if the single select is a specific name (my wife) to add the amount in the “paid to supplier” and the “profit” column for that row into a “final profit” column? Thanks so much. These videos and the way you explain this has really been helpful.

[arielsigler]

Very Nice ! - I love how you explain things.
Gareth, in airtable community I was introduced to "integromat" . Looks like a really cool tool that puts Zapier in the Shade (sorry to say) Since it automates also when a record is changed and not only when being created . I wanted to point out your attention to this tool. I think you will love it. ( I don't have any business connection with the company and I am not familiar with the developers)

[chrishicks]

I've been struggling with this. I'm running a time logging application specific for events. I have a Start/End/Break Start/Break End date fields. I'd like the Status to switch to different things (active/break/done for the day) based on info entering those fields (Start =active). I can write simple if statements, but nested they don't work. I think because the statements reference different fields? Anything you can tell me that I might be doing wrong?

[leetal]

HI Garath, thank you for the video, it is super informative, however there is something I'm missing.
When you explained the IF() formula you were able to use a nested statement to include both the status and checkbox fields - meaning all possible options are covered and the output of the formula is as accurate as it can be.
on the other hand, with the SWITCH() formula, you only used the status field in the statement you wrote (which might be a cleaner way of writing a formula but doesn't give the same "coverage" and accuracy.) Is there a way to use a SWITCH() formula that takes into account both the status and checkbox fields? Thanks!

[clairebarnett]

Hi Gareth - Just started using Airtable and it's changed my workflow dramatically - Your videos are such a massive help. I'm struggling with a formula for past date due "OVERDUE" but if marked as paid "PAID". I have tried sooo many connotations (over 4 hours today 😭) of the formula but it's just not working - can you point me in the right direction or can I employ your help?

[bill.scully]

Hi Garett, Another great video! I see you selecting the field types and formula starting text super fast. I’m guessing there are short cuts. IS there a video on that? If not you should do one. - Bill

[HunterReynolds]

Thanks for teaching me about Switch()!