IF function too short, what do I do now?

[mj1000]

Please help. How do I work this formula into a LOOKUP function. I need 12 nested IF functions and of course I am only allowed 7. Thanks in advance

=IF(AND(C4<=4,C5="fr",C6="n"),'Rate Sheet'!C4,IF(AND(C4<=7,C5="fr",C6="n"),'Rate Sheet'!D4,IF(AND(C4<=10,C5="fr",C6="n"),'Rate Sheet'!E4,IF(AND(C4<=4,C5="fr",C6="y"),'Rate Sheet'!C5,IF(AND(C4<=7,C5="fr",C6="y"),'Rate Sheet'!D5,IF(AND(C4<=10,C5="fr",C6="y"),'Rate Sheet'!E5,IF(AND(C4<=4,C5="nc",C6="n"),'Rate Sheet'!C7,IF(AND(C4<=7,C5="nc",C6="n"),'Rate Sheet'!D7,IF(AND(C4<=10,C5="nc",C6="n"),'Rate Sheet'!E7,IF(AND(C4<=4,C5="nc",C6="y"),'Rate Sheet'!C8,IF(AND(C4<=7,C5="nc",C6="y"),'Rate Sheet'!D8,IF(AND(C4<=10,C5="nc",C6="y"),'Rate Sheet'!E8,))))))))))))

 

[KenWright]

How about:-

=OFFSET('Rate Sheet'!$B$3,MATCH(C5&C6,{"frn","fry",0,"ncn","ncy"},0),MATCH(MAX(0,C4-0.000001),{0,4,7,10}))

 

[PA HS Teacher]

Untested:

=If(C4<=10, Vlookup(Min((C4<=4)*1,(C4<=7)*2,(C4<=10)*3)&C5&C6,
{“1frn”,'RateSheet'!C4;
“2frn”,'RateSheet'!D4;
“3frn”,'RateSheet'!E4;
“1frn”,'RateSheet'!C5;
“2frn”,'RateSheet'!D5;
“3frn”,'RateSheet'!E5;
“1ncn”,'RateSheet'!C7;
“2ncn”,'RateSheet'!D7;
“3ncn”,'RateSheet'!E7;
“1ncy”,'RateSheet'!C8;
“2ncy”,'RateSheet'!D8;
“3ncy”,'Rate Sheet'!E8},0), “C4 is bigger than 10”)