Can I simplify an if-statement for an Airtable formula?

Question

I'm pretty new to writing formulas to manipulate data. I'm using Airtable to gather survey feedback and assign each response a category based on 2 scores provided. Each or two scores can be -4, -2, 0, 2, or 4. Depending on the combination of scores, the response will go into one of six categories.

To do this, I've written an If/And formula (that works), but I'm wondering if there's a simpler and cleaner way to achieve this:

IF(AND({Functional score}=4,{Dysfunctional score}=4),"Q",
IF(AND({Functional score}=-4,{Dysfunctional score}=-4),"Q",
IF(AND({Functional score}=2,{Dysfunctional score}=2),"Q",
IF(AND({Functional score}=-2,{Dysfunctional score}=-2),"Q",
IF(AND({Functional score}=0,{Dysfunctional score}=0),"I",
IF(AND({Functional score}=4,{Dysfunctional score}=2),"A",
IF(AND({Functional score}=4,{Dysfunctional score}=0),"A",
IF(AND({Functional score}=4,{Dysfunctional score}=-2),"A",
IF(AND({Functional score}=4,{Dysfunctional score}=-4),"P",
IF(AND({Functional score}=2,{Dysfunctional score}=4),"R",
IF(AND({Functional score}=2,{Dysfunctional score}=0),"I",
IF(AND({Functional score}=2,{Dysfunctional score}=-2),"I",
IF(AND({Functional score}=2,{Dysfunctional score}=-4),"M",
IF(AND({Functional score}=0,{Dysfunctional score}=4),"R",
IF(AND({Functional score}=0,{Dysfunctional score}=2),"I",
IF(AND({Functional score}=0,{Dysfunctional score}=-2),"I",
IF(AND({Functional score}=0,{Dysfunctional score}=-4),"M",
IF(AND({Functional score}=-2,{Dysfunctional score}=4),"R",
IF(AND({Functional score}=-2,{Dysfunctional score}=2),"I",
IF(AND({Functional score}=-2,{Dysfunctional score}=0),"I",
IF(AND({Functional score}=-4,{Dysfunctional score}=4),"R",
IF(AND({Functional score}=-4,{Dysfunctional score}=2),"R",
IF(AND({Functional score}=-4,{Dysfunctional score}=0),"R",
IF(AND({Functional score}=-4,{Dysfunctional score}=-2),"R",
"Q"))))))))))))))))))))))))

Thank you for your help!

Answer 1

There are a variety of ways to do it in excel. Since this question is tagged with excel-formula I will provide some options using excel formulas.

The first two option will depend on laying out your results out in a matrix. This works because you have two options: FUNCTIONAL and DYSFUNCTIONAL

In this example the data is assumed to be layed out as follows:

Option 1

Use a combination of INDEX and MATCH. The INDEX formula will return the address of the cell/range for a given row and column number for a selected range. It uses the following format:

INDEX(SELECTED RANGE,ROW NUMBER, COLUMN NUMBER)

It is important to note that the row number and the column number are in reference to the selected range, and not referencing the column of the worksheet. The only case this would be the same for both is if the selected range started in A1.

Based on this information the start of the formula will look like:

=INDEX($D$4:$H$8,

Because I usually do not want my selected range to change as I copy it to another cell, I have a habit of locking the range cell references with $.

The next part that is needed is to supply a row number. To do this the MATCH function works well. The MATCH function will look for a given value in a defined range and return its position in that range. you can also set the type of look up with you want in terms of an approximate, and a nearest too type search or a reverse nearest to function...note that for the later two to work the range needs to be sorted, and for the later to work that range needs to be sorted in reverse order. The general format of MATCH is as follows:

MATCH(WHAT YOU ARE LOOKING FOR, RANGE TO LOOK, SEARCH OPTION)

In our case you want to match the dysfunction score. In this case the dysfunctional score is sitting in cell D12. Therefore the MATCH formula should look like:

=MATCH($D$12,$C$4:$C$8,0)

The 0 at the end tells MATCH that an exact match is desired. Since that returns the row number, it can be placed directly into the INDEX formula for the row number, and the formula now looks like:

=INDEX($D$4:$H$8,MATCH($D$12,$C$4:$C$8,0),

Now the column number needs to be determined. Again the MATCH function can be used and this time the range to look in is horizontal instead of vertical. Since the functional score is in D11, the formula should look like:

=MATCH($D$11,$D$3:$H$3,0)

Since that returns the column number of the selected range, we can dump that directly into our INDEX formula which finishes off and look like:

=INDEX($D$4:$H$8,MATCH($D$12,$C$4:$C$8,0),MATCH($D$11,$D$3:$H$3,0))

Option 2

This option is very similar and work using INDEX and a bit of math manipulation based on your very specific score options. The table lay out is very specificfor this example going from smallest to largest. Also all your scores are divisible by 2 to get an increment of 1 between scores, -2, -1, 0, 1, 2. Now if you add 3 to that, you then get 1, 2, 3, 4, 5 which happens to correspond to your options for rows and columns. So if we replace MATCH from option 1 with that little bit of math, then the INDEX formula becomes:

=INDEX($D$4:$H$8,$D$12/2+3,$D$11/2+3)

Option 3

This is just a nested IF similar to yours but groups the results together instead of listing every single cell option. The logic of the individual IF functions do not work on their own. They do however work in the order they are nested in since if a TRUE condition results in one of the previous IF functions, a letter is returned, and the remaining levels of the IF function do not get checked. Massive nested IF functions can be hard to read, let alone hard to maintain when you come back in a month or more and try to figure out what you did. Even worse for the poor sap that is reading it for the first time trying to figure out what is going on. So a nested IF function grouping your results together could look like the following:

=IF(AND(D11=D12,D11<>0),"Q",IF(OR(D11=-4,D12=4),"R",IF(AND(D11=4,D12=-4),"P",IF(D12=-4,"M",IF(D11=4,"A","I")))))

There are other options as well that can use array functions. And if your data had more than 2 options (FUNCTIONAL/DYSFUNCTIONAL) then there are other options for laying out the data. For excel I would recommend the use of the table for readability and maintainability but you will have to decide what works best for your situation.

Adjust cell and range references to suit your needs.

Option 4

Use AGGREGATE to do the job of MATCH. Since AGGREGATE return the worksheet column number, it needs to be adjusted to the selected range by subtracting the header row/column number. Without any further explanation the formula is:

=INDEX($D$4:$H$8,AGGREGATE(15,6,ROW($C$4:$C$8)/($C$4:$C$8=$D$12),1)-ROW($C$3),AGGREGATE(15,6,COLUMN($D$3:$H$3)/($D$3:$H$3=$D$11),1)-COLUMN($C$3))