Help

Help with Long IF or SWITCH formula

Topic Labels: Formulas
1672 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Julia_Hamm
4 - Data Explorer
4 - Data Explorer

Hello,

I am trying to create a formula that automatically assigns a Team based on Column 1 which contains company names. I am running into trouble, it seems, due to the length of my formulas. I am wondering if anyone can assist.

Below is the formula I created in Word, it works in Airtable when I shorten it to cover only half the companies. I have tested with several different orders to make sure it isn’t just the next company in the series that is causing an error. I have about 20 companies I am assigning 1 of 2 teams to.

SWITCH({Distributor Name},
“Company Name”,“Team 1”,
“Company Name”,“Team 1”,
“Company Name”,“Team 1”,
“Company Name”,“Team 1”,
“Company Name”,“Team 1”,

“Company Name”,“Team 2”)

I have also tried IF formulas such as the below, however no matter how I’ve formatted them they automatically cut it short and only follow the first 2 expressions.
IF({Distributor Name}=“Company Name”,“Team 1”),
IF({Distributor Name}=“Company Name”,“Team 1”),
IF({Distributor Name}=“Company Name”, “Team 2”),
IF({Distributor Name}=“Company Name”, “Team 2”))

Does anyone have any suggestions?

1 Reply 1
AlliAlosa
10 - Mercury
10 - Mercury

Couple suggestions :slightly_smiling_face:

Your syntax for the SWITCH() formula appears correct… but perhaps when you are pasting the formula into Airtable after building it in Word, you’re pasting smart quotes instead of straight quotes. Smart quotes “” appear curved and will cause an error in Airtable. Try deleting and replacing any quotes you see that aren’t perfectly straight lines.

If you choose to go with the IF() formula (though SWITCH(), in my opinion, is the better choice here), I would use it in conjunction with OR() to make it a bit cleaner. i.e.

IF(OR({Distributor Name} = "Company Name 1", {Distributor Name} = "Company Name 2", {Distrubtor Name} = "Company Name 3"), "Team 1", IF(OR({Distributor Name} = "Company Name 4", {Distributor Name} = "Company Name 5"), "Team 2"))

All of this being said… if I were you, I would simply create a new table called [Teams], and link each company manually. This would give you more flexibility down the line, especially if you want to group companies by team.

Hope this helps!