How can I improve my nested IF formula in Airtable?

Other Questions Airtable
1

I’m trying to use Airtable to categorize input from survey responses based on two scores. Each of these scores can take the values -4, -2, 0, 2, or 4. Depending on how these scores combine, I want to put responses into one of six categories.

Right now, I’m relying on many nested IF conditions, which is functional but quite complicated. Is there an easier way to write this formula?

IF(AND({Score1}=4,{Score2}=4),"Category A",
IF(AND({Score1}=-4,{Score2}=-4),"Category A",
IF(AND({Score1}=2,{Score2}=2),"Category A",
IF(AND({Score1}=-2,{Score2}=-2),"Category A",
IF(AND({Score1}=0,{Score2}=0),"Category B",
IF(AND({Score1}=4,{Score2}=2),"Category C",
IF(AND({Score1}=4,{Score2}=0),"Category C",
IF(AND({Score1}=4,{Score2}=-2),"Category C",
IF(AND({Score1}=4,{Score2}=-4),"Category D",
IF(AND({Score1}=2,{Score2}=4),"Category E",
IF(AND({Score1}=2,{Score2}=0),"Category B",
IF(AND({Score1}=2,{Score2}=-2),"Category B",
IF(AND({Score1}=2,{Score2}=-4),"Category F",
IF(AND({Score1}=0,{Score2}=4),"Category E",
IF(AND({Score1}=0,{Score2}=2),"Category B",
IF(AND({Score1}=0,{Score2}=-2),"Category B",
IF(AND({Score1}=0,{Score2}=-4),"Category F",
IF(AND({Score1}=-2,{Score2}=4),"Category E",
IF(AND({Score1}=-2,{Score2}=2),"Category B",
IF(AND({Score1}=-2,{Score2}=0),"Category B",
IF(AND({Score1}=-4,{Score2}=4),"Category E",
IF(AND({Score1}=-4,{Score2}=2),"Category E",
IF(AND({Score1}=-4,{Score2}=0),"Category E",
IF(AND({Score1}=-4,{Score2}=-2),"Category E",
"Category A")))))))))))))))))))))))))

I’d appreciate any tips for simplifying this!

2

I ran into the same thing with complex categorization logic. Calculated fields are perfect for this - just don’t cram everything into one giant formula. Break it into chunks that make sense. Start with a formula field that catches your diagonal patterns where Score1 equals Score2, then handle the weird cases separately. Try something like IF(Score1 = Score2, “Category A”, SWITCH(TRUE(), AND(Score1 = 4, Score2 > -4), “Category C”, Score1 = -4, “Category E”, “Category B”)). Way easier to read and debug later. The trick is spotting the patterns in your data - equal scores usually hit Category A, negative Score1 values end up in Category E, etc. Once you see those patterns, build your formula around them instead of treating every combo like it’s unique.

3

wow, thats a mess lol! try SWITCH instead of nested IFs. you could also create a lookup table with all the combos and use a vlookup-style formula. way cleaner than that monster formula you’ve got there.

4

Been there. You’re overcomplicating this.

Create a calculated field that adds your scores together. Your formula has clear patterns:

SWITCH(TRUE(),
{Score1} = {Score2}, "Category A",
{Score1} + {Score2} = 0, "Category B", 
{Score1} = 4, "Category C",
{Score1} = -4, "Category E",
"Category F")

This handles most cases immediately. Equal scores = Category A. Sum to zero = Category B. Score1 at 4 = Category C. Score1 at -4 = Category E.

Add conditions for edge cases, but this structure beats what you’ve got now.

I’ve built similar survey systems - find the logical rules behind your categories instead of hardcoding every combination. Way easier when you add new score values later.

5

Here’s another approach - create a separate mapping table in your base. I’ve used this tons of times when the categorization logic gets complex or changes frequently.

Make a new table with three columns: Score1, Score2, and Category. Drop all your combinations in there, then use:

LOOKUP(Score1 & Score2, {Mapping Table Score1} & {Mapping Table Score2}, {Mapping Table Category})

This keeps your logic completely separate from your formula. Need to tweak categories or add new score combos? Just update the mapping table instead of fighting with formula syntax. Learned this the hard way after wasting hours debugging nested conditionals that turned into a nightmare when requirements changed.

6

Pro tip: use helper columns to split up complex logic. First column calculates your score combinations, then reference it with a simpler formula. You won’t repeat the same logic everywhere and debugging becomes much easier when things break.

7

You could also use math operations on your scores to create categories. Looking at your pattern, try calculating the sum or difference of your scores as a basis for grouping. Like Score1 + Score2 might give you natural groupings that match your categories. I’ve found this works great with survey data since it often reflects why certain combinations belong together. You could try SWITCH(Score1 + Score2, 8, “Category A”, -8, “Category A”, 4, “Category A”, -4, “Category A”, 0, “Category B”, 6, “Category C”) and so on. This cuts down the combinations you need to define and makes your formula way shorter. The catch is it only works if your categorization follows mathematical relationships, but it’s worth checking if that fits your survey scoring system.

8

I’ve dealt with similar categorization nightmares before. Just concatenate your scores into a single lookup key.

Try this:

SWITCH({Score1} & "|" & {Score2},
"4|4", "Category A",
"-4|-4", "Category A",
"2|2", "Category A",
"-2|-2", "Category A",
"0|0", "Category B",
"4|2", "Category C",
"4|0", "Category C",
"4|-2", "Category C",
"4|-4", "Category D",
"2|4", "Category E",
"2|0", "Category B",
"2|-2", "Category B",
"2|-4", "Category F",
"0|4", "Category E",
"0|2", "Category B",
"0|-2", "Category B",
"0|-4", "Category F",
"-2|4", "Category E",
"-2|2", "Category B",
"-2|0", "Category B",
"-4|4", "Category E",
"-4|2", "Category E",
"-4|0", "Category E",
"-4|-2", "Category E",
"Category A")

This creates a unique string for each score combo and matches it directly. Way easier to read and maintain than your nested IF hell.