How can I use more than 64 levels of nesting

[nskatoch]

=IF(AND(P1=1,B37=HPE!C5),HPE!C6,IF(AND(P1=1,B37=HPE!D5),HPE!D6,IF(AND(P1=1,B37=HPE!E5),HPE!E6,IF(AND(P1=1,B37=HPE!F5),HPE!F6,IF(AND(P1=1,B37=HPE!G5),HPE!G6,IF(AND(P1=2,B37=HPE!C5),HPE!C7,IF(AND(P1=2,B37=HPE!D5),HPE!D7,IF(AND(P1=2,B37=HPE!E5),hpe!E7,IF(AND(P1=2,B37=HPE!F5),HPE!F7,IF(AND(P1=2,B37=HPE!G5),HPE!G7,IF(AND(P1=3,B37=HPE!C5),HPE!C8,IF(AND(P1=3,B37=HPE!D5),HPE!D8,IF(AND(P1=3,B37=HPE!E5),HPE!E8,IF(AND(P1=3,B37=HPE!F5),HPE!F8,IF(AND(P1=3,B37=HPE!G5),HPE!G8,IF(AND(P1=4,B37=HPE!C5),HPE!C9,IF(AND(P1=4,B37=HPE!D5),HPE!D9,IF(AND(P1=4,B37=HPE!E5),HPE!E9,IF(AND(P1=4,B37=HPE!F5),HPE!F9,IF(AND(P1=4,B37=HPE!G5),HPE!G9,IF(AND(P1=5,B37=HPE!C5),HPE!C10,IF(AND(P1=5,B37=HPE!D5),HPE!D10,IF(AND(P1=5,B37=HPE!E5),HPE!E10,IF(AND(P1=5,B37=HPE!F5),HPE!F10,IF(AND(P1=5,B37=HPE!G5),HPE!G10,IF(AND(P1=6,B37=HPE!C5),HPE!C11,IF(AND(P1=6,B37=HPE!D5),HPE!D11,IF(AND(P1=6,B37=HPE!E5),COSA!E11,IF(AND(P1=6,B37=HPE!F5),HPE!F11,IF(AND(P1=6,B37=HPE!G5),HPE!G11,IF(AND(P1=7,B37=HPE!C5),HPE!C12,IF(AND(P1=7,B37=HPE!D5),HPE!D12,IF(AND(P1=7,B37=HPE!E5),COSA!E12,IF(AND(P1=7,B37=HPE!F5),HPE!F12,IF(AND(P1=7,B37=HPE!G5),HPE!G12,IF(AND(P1=8,B37=HPE!C5),HPE!C13,IF(AND(P1=8,B37=HPE!D5),HPE!D13,IF(AND(P1=8,B37=HPE!E5),HPE!E13,IF(AND(P1=8,B37=HPE!F5),HPE!F13,IF(AND(P1=8,B37=HPE!G5),HPE!G13,IF(AND(P1=9,B37=HPE!C5),HPE!C14,IF(AND(P1=9,B37=HPE!D5),HPE!D14,IF(AND(P1=9,B37=HPE!E5),HPE!E14,IF(AND(P1=9,B37=HPE!F5),HPE!F14,IF(AND(P1=9,B37=HPE!G5),HPE!G14,IF(AND(P1=10,B37=HPE!C5),HPE!C15,IF(AND(P1=10,B37=HPE!D5),HPE!D15,IF(AND(P1=10,B37=HPE!E5),HPE!E15,IF(AND(P1=10,B37=HPE!F5),HPE!F15,IF(AND(P1=10,B37=HPE!G5),HPE!G15,IF(AND(P1=11,B37=HPE!C5),HPE!C16,IF(AND(P1=11,B37=HPE!D5),HPE!D16,IF(AND(P1=11,B37=HPE!E5),HPE!E16,IF(AND(P1=11,B37=HPE!F5),HPE!F16,IF(AND(P1=11,B37=HPE!G5),HPE!G16,IF(AND(P1=12,B37=HPE!C5),HPE!C17,IF(AND(P1=12,B37=HPE!D5),HPE!D17,IF(AND(P1=12,B37=HPE!E5),HPE!E17,IF(AND(P1=12,B37=HPE!F5),HPE!F17,IF(AND(P1=12,B37=HPE!G5),HPE!G17,IF(AND(P1=13,B37=HPE!C5),HPE!C18,IF(AND(P1=13,B37=HPE!D5),HPE!D18,IF(AND(P1=13,B37=HPE!E5),HPE!E18,IF(AND(P1=13,B37=HPE!F5),HPE!F18," "))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))In above formula HPE is the of worksheet as below:Col. → A B C D E F GRow No. Roll No Name of Student Sports N.C.C. Yoga First Aid Gardening6 1 John A B A B A7 2 Mike B B A C A8 3 Eela B A B D B9 4 Suo C A A A B . . 50In next worksheet P1 is cell which shows the Roll No. in a list and B37 cell shows the selected activity (i.e. sports, NCC, Yoga, First aid, Gardening)In my formula If Roll No.: 1 i.e. P1=1 and activity is one of the above i.e. B37=HPE!C5 then result is HPE!C6 i.e. A,If P1=1, B37=HPE!D5 then result is result is HPE!D6 i.e. BIf P1=1, B37=HPE!E5 then result is result is HPE!E6 i.e. AIf P1=1, B37=HPE!F5 then result is result is HPE!F6 i.e. BIf P1=1, B37=HPE!G5 then result is result is HPE!G6 i.e. AIf P1=2, B37=HPE!C5 then result is result is HPE!C7 i. e. BIf P1=2, B37=HPE!D5 then result is result is HPE!D7 i.e. D and so onIt runs upto if(and(P1=13,b37=HPE!F5),HPE!F18, “”))))))) successfullyAfter this an error is flashed that The specified formula cannot be entered because it uses more than 64 levels of nesting. I want run this formula upto Roll No. 50.

 

[CROY1985]

Did you try the formula i sent you yesterday?

=INDEX(HPE!C:G,P1+1,MATCH(B37,HPE!C$5:G$5))

 

[RoryA]

This is a duplicate of this thread so I'm locking this.

In future, please do not post duplicate questions and please have the courtesy to respond to those trying to help you - otherwise they will probably stop trying!