How to optimize nested IF conditions in Airtable formulas?

Other Questions Airtable
1

I’m working with Airtable to categorize customer feedback based on two rating values. Each rating can be -4, -2, 0, 2, or 4, and I need to assign one of six categories (Q, I, A, P, R, M) based on the combination of these two scores.

My current formula works but feels overly complex with all the nested IF statements. Here’s what I have:

IF(AND({Rating A}=4,{Rating B}=4),"Q",
IF(AND({Rating A}=-4,{Rating B}=-4),"Q",
IF(AND({Rating A}=2,{Rating B}=2),"Q",
IF(AND({Rating A}=-2,{Rating B}=-2),"Q",
IF(AND({Rating A}=0,{Rating B}=0),"I",
IF(AND({Rating A}=4,{Rating B}=2),"A",
IF(AND({Rating A}=4,{Rating B}=0),"A",
IF(AND({Rating A}=4,{Rating B}=-2),"A",
IF(AND({Rating A}=4,{Rating B}=-4),"P",
IF(AND({Rating A}=2,{Rating B}=4),"R",
IF(AND({Rating A}=2,{Rating B}=0),"I",
IF(AND({Rating A}=2,{Rating B}=-2),"I",
IF(AND({Rating A}=2,{Rating B}=-4),"M",
IF(AND({Rating A}=0,{Rating B}=4),"R",
IF(AND({Rating A}=0,{Rating B}=2),"I",
IF(AND({Rating A}=0,{Rating B}=-2),"I",
IF(AND({Rating A}=0,{Rating B}=-4),"M",
IF(AND({Rating A}=-2,{Rating B}=4),"R",
IF(AND({Rating A}=-2,{Rating B}=2),"I",
IF(AND({Rating A}=-2,{Rating B}=0),"I",
IF(AND({Rating A}=-4,{Rating B}=4),"R",
IF(AND({Rating A}=-4,{Rating B}=2),"R",
IF(AND({Rating A}=-4,{Rating B}=0),"R",
IF(AND({Rating A}=-4,{Rating B}=-2),"R",
"Q"))))))))))))))))))))))))

Is there a more elegant way to handle this logic without so many nested conditions?

2

I’ve tackled similar complex categorization in Airtable before. What worked best for me was concatenating the two rating values and using SWITCH to map the combinations to categories:

SWITCH(
  {Rating A} & "|" & {Rating B},
  "4|4", "Q",
  "-4|-4", "Q",
  "2|2", "Q",
  "-2|-2", "Q",
  "0|0", "I",
  "4|2", "A",
  "4|0", "A",
  "4|-2", "A",
  "4|-4", "P",
  "2|4", "R",
  "2|0", "I",
  "2|-2", "I",
  "2|-4", "M",
  "0|4", "R",
  "0|2", "I",
  "0|-2", "I",
  "0|-4", "M",
  "-2|4", "R",
  "-2|2", "I",
  "-2|0", "I",
  "-4|4", "R",
  "-4|2", "R",
  "-4|0", "R",
  "-4|-2", "R",
  "Q"
)

This cuts out all the nested structure and makes everything way easier to read and maintain. Performance is basically the same, but readability improves massively.

3

I’d create a lookup table in a separate table. Had the same headache with product categorization logic that got crazy complex.

Make a new table called “Rating Categories” with three columns:

  • Rating A (number)
  • Rating B (number)
  • Category (single select with Q, I, A, P, R, M)

Dump all 25 combinations in there. Then use this lookup formula in your main table:

LOOKUP({Rating A} & "|" & {Rating B}, {Rating Categories Table}, {Rating A} & "|" & {Rating B}, {Category})

Keeps your logic separate from your data. When requirements change (they always do), just update the lookup table instead of wrestling with formula syntax.

This scales way better with complex business rules that keep evolving. Plus teammates can tweak categories without touching formulas.