Using IF and AND function

Question

I am trying to use the IF and AND function in excel for values in two different cells. I have 25 conditions.

Below is the formula I've created but it keeps on saying there's an error.

IF(AND(A10=“A”,B10=1),11,IF(AND(A=“A”,B10=2),16,IF(AND(A10=“A”,B10=3),20,IF(AND(A10=“A”,B10=4),23,IF(AND(A10=“A”,B10=5),25,IF(AND(A10=“B”,B10=1),7,IF(AND(A10=“B”,B10=2),12,IF(AND(A10=“B”,B10=3),17,IF(AND(A10=“B”,B10=4),21,IF(AND(A10=“B”,B10=5),24,IF(AND(A10=“C”,B10=1),4,IF(AND(A10=“C”,B10=2),8,IF(AND(A10=“C”,B10=3),13,IF(AND(A10=“C”,B10=4),18,IF(AND(A10=“C”,B10=5),22,IF(AND(A10=“D”,B10=1),2,IF(AND(A10=“D”,B10=2),5,IF(AND(A10=“D”,B10=3),9,IF(AND(A10=“D”,B10=4),14,IF(AND(A10=“D”,B10=5),19,IF(AND(A10=“E”,B10=1),1,IF(AND(A10=“E”,B10=2),3,IF(AND(A10=“E”,B10=3),6,IF(AND(A10=“E”,B10=4),10,15))))))))))))))))))))))))))))))))))))))))))))))))

I expected the output to be, for example; if cell1 is "A" and cell2 is 1 the result should be 11.

Answer 1

I would highly advise a lookup table. Simply have all of your options listed out with their desired results and find them with a criteria search, such as the use of sumifs function.

For example, if you paste J1:L25 your possibilities:

A   1   11
A   2   16
A   3   20
A   4   23
A   5   25
B   1   7
B   2   12
B   3   17
B   4   21
B   5   24
C   1   4
C   2   8
C   3   13
C   4   18
C   5   22
D   1   2
D   2   5
D   3   9
D   4   14
D   5   19
E   1   1
E   2   3
E   3   6
E   4   10
E   5   15

You can then place the formula =SUMIFS($L$1:$L$25,$J$1:$J$25,$A$10,$K$1:$K$25,$B$10) to return your desired value. That is, =SUMIFS(range_of_results, criteria_range_of_A-E, A10, criteria_range_of_1-5, B10)