Formula If or Switch Help

Topic Labels: Automations Formulas
527 5
Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast

Hey I am trying to set up a formula for the below problem. I am provided with a list of names in the 'home' column and certain entries I need to change to end up like the 'bot name'. 

I have tried switch/If functions and I just get errors. 



5 Replies 5
'Team ABC FC', 'ABC FC',
'Team Hij', 'Hij')


How many different values are there in the {Home} text field? Do you have a complete list of all possible values? If you have only a limited number of possible values, you can use the pattern in the SWITCH() function that Alexey pointed out. However, if you have many values, the values might change, or new values might be added, you may need a different approach so you don't have to constantly update the formula whenever a new name is entered.

I can't see the logic used to determine the {Bot Name}. It seems like most of the time you strip out the word "Team" from the beginning and ensure that "FC" is at the end. This logic could be implemented with a formula, but it would be formula with multiple nested functions, not a simple SWITCH() or IF(). However, then you have "Team DEF" which doesn't fit that pattern, so you may need something more complex.

Does a single value in the {Home} text field occur in multiple rows? If this is commonplace, consider using a linked table with one field for the {Home} field value and another field for the {Bot Name} and use a lookup field for the {Bot Name}. Do the three parts of the {Home} field value each have meaning? If so, consider using three different fields for the individual parts, and then consolidate the info in a formula field.

5 - Automation Enthusiast
5 - Automation Enthusiast

I have potential 20 in these base but have about 80 or more across bases, They only change once a year as they are sporting teams. 

Your lookup option may be easier to manage. How would that work, I have only been using airtable in very basic functionality.

Thanks for your help so far. 


Thanks, I was struggling how to type it out, that has worked but going to also look at the other option as there are a lot of names I need to add across all bases.

Here are the Airtable support pages on linked record fields and lookup fields. (They work together.)

This support page has a video on creating a new linked table by converting an existing field to a linked record field.