Writing a Formula

[rbronson]

I'm trying to write an IF..AND statement that will return "37" if A2 is "Regular" and C2 is "1" and will return "41" is A2 is "Regular" and C2 is "2 and will return "40" if A2 is "Honors" and C2 is "1" and will return "48" if A2 is "Honors" and C2 is "2." What I have so far is "=IF((AND(A2=”Regular”),(C2=1)),37,IF(AND(A2=”Regular”),(C2=2)),41,IF(AND(A2=”Honors”),(C2=1)),40,IF(AND(A2=”Honors),(C2=2)),48)"  I'm hoping the problem is just the parentheses but so far this formula doesn't work. 

[#2]

=IF(
AND(A2=”Regular”,C2=1),37,
IF(
AND(A2=”Regular”C2=2),41,
IF(
AND(A2=”Honors”,C2=1),40,
IF(
AND(A2=”Honors,C2=2),48,
“No Result”
)

[#3]

@rbronson you have some problems with () and "" and I added a "n/a" just in case.  try this:

=IF(AND(A2="Regular",C2=1),37,IF(AND(A2="Regular",C2=2),41,IF(AND(A2="Honors",C2=1),40,IF(AND(A2="Honors",C2=2),48,"n/a"))))

Note you could also use the IFS() statement:

=IFS(AND(A2="Regular",C2=1),37,AND(A2="Regular",C2=2),41,AND(A2="Honors",C2=1),40,AND(A2="Honors",C2=2),48)

and IF the only options for A2 are Regular or Honors and for C2 are 1 or 2 then you could make it shorter but it may be less easy to read:

=IF(A2="Regular",37+(C2=2)*4,40+(C2=2)*8)

[#4]

Thanks @Wyn Hopkins 

[#5]

Thanks @mtarler 

[#6]

@rbronson 

One more variant

=MMULT(MMULT(--(A2={"Regular","Honors"}),{37,41;40,48}),--(C2={1;2}))

[#7]

Hi 

A cleaner way of doing this would be to have a small reference table and use a SUMIFS