Too long IF function

Question

Hi all

Would anyone have a solution for this problem. I have two features which together should give a result based on a matrix like this:

I would need a formula for yellow cells.

So far I have tried:

=IF(AND(A1=1;OR(B1=1;B1=2;B1=3));"I";IF(AND(A1=2;OR(B1=1;B1=2));"I";IF(AND(A1=3;B1=1);"I";IF… etc.

But that end up to an error: "This formula uses more levels of nesting than you can use in the current file format"

I also tried to use numbers A * B (equal to, smaller/greater than) but the problem appear with numbers 4, 8 and 10 since the matrix is not linear.

Answer 1

As per my screen shot, give this formula at K2 and copy it down.

=VLOOKUP(I2,$B$2:$G$7,MATCH(J2,$B$2:$G$2,0),0)

Answer 2

Let's say your data are in A1:F6 on Sheet1, and the numbers you're looking for in A1 and B1 on another sheet.

=INDEX(Sheet1!$B$2:$F$6,MATCH(A1,Sheet1!$A$2:$A$6,0),MATCH(B1,Sheet1!$B$1:$F$1,0))

This formula can be filled down.

Answer 3

Thank you very much! This one solved my problem.