Need for more than 64 levels of nested - Simplify formula

Copper Contributor

Hai Guys,

 

i have not experience with that case, so i need your help with this, i have very long formula more than 64 levels of nested, and i need to simplify the formula, an di will copy and paste my formule here but although it might look very complicated, it is pretty much the same thing over and over again but with different values. I need to find a way to simplify it, so I don't get the error of 64 levels of nested if I add more.and my formula is from some of table that use vlookup and hlookup  Here is the example table:

 Age 
Year20 - 3536-55
12,502,98
22,503,22
333,22
442

 and here is my formula

 

=IF(AND(AD7>=20;AD7<=55;AM7=1);2,509;

IF(AND(AD7>=20;AD7<=55;AM7=2);5,000;

IF(AND(AD7>=20;AD7<=55;AM7=3);7,509;

IF(AND(AD7>=20;AD7<=55;AM7=4);10,018;

IF(AND(AD7>=20;AD7<=55;AM7=5);12,509;

IF(AND(AD7>=20;AD7<=55;AM7=6);15,018;

IF(AND(AD7>=20;AD7<=55;AM7=7);17,527;

IF(AND(AD7>=20;AD7<=55;AM7=8);20,017;

IF(AND(AD7>=20;AD7<=55;AM7=9);22,526;

IF(AND(AD7>=20;AD7<=55;AM7=10);25,053;

IF(AND(AD7>=20;AD7<=55;AM7=11);27,544;

IF(AND(AD7>=20;AD7<=55;AM7=12);30,053;

IF(AND(AD7>=20;AD7<=55;AM7=13);32,562;

IF(AND(AD7>=20;AD7<=55;AM7=14);35,053;

IF(AND(AD7>=20;AD7<=55;AM7=15);37,562;

IF(AND(AD7>=56;AD7<=64;AM7=1);4,819;

IF(AND(AD7>=56;AD7<=64;AM7=2);10,018;

IF(AND(AD7>=56;AD7<=64;AM7=3);15,018;

IF(AND(AD7>=56;AD7<=64;AM7=4);20,017;

IF(AND(AD7>=56;AD7<=64;AM7=5);25,035;

IF(AND(AD7>=56;AD7<=64;AM7=6);30,035;

IF(AND(AD7>=56;AD7<=64;AM7=7);35,035;

IF(AND(AD7>=56;AD7<=64;AM7=8);40,053;

IF(AND(AD7>=56;AD7<=64;AM7=9);45,053;

IF(AND(AD7>=56;AD7<=64;AM7=10);52,687;

IF(AND(AD7>=56;AD7<=64;AM7=11);53,390;

IF(AND(AD7>=56;AD7<=64;AM7=12);53,432;

IF(AND(AD7>=56;AD7<=64;AM7=13);55,495;

IF(AND(AD7>=56;AD7<=64;AM7=14);57,870;

IF(AND(AD7>=56;AD7<=64;AM7=15);60,032;

IF(AND(AD7>=65;AD7<=69;AM7=1);4,819;

IF(AND(AD7>=65;AD7<=69;AM7=2);10,018;

IF(AND(AD7>=65;AD7<=69;AM7=3);15,018;

IF(AND(AD7>=65;AD7<=69;AM7=4);20,017

;IF(AND(AD7>=65;AD7<=69;AM7=5);25,035;

IF(AND(AD7>=65;AD7<=69;AM7=6);30,035;

IF(AND(AD7>=65;AD7<=69;AM7=7);35,035;

IF(AND(AD7>=65;AD7<=69;AM7=8);40,053;

IF(AND(AD7>=65;AD7<=69;AM7=9);45,053;

IF(AND(AD7>=65;AD7<=69;AM7=10);52,687;

IF(AND(AD7=70;AM7=1);10,88;IF(AND(AD7=71;AM7=1);11,05;IF(AND(AD7=72;AM7=1);12,48;IF(AND(AD7=73;AM7=1);13,33;IF(AND(AD7=74;AM7=1);14,67;IF(AND(AD7>=75;AD7<=79;AM7=1);48,96;IF(AND(AD7=70;AM7=2);21,96;IF(AND(AD7=71;AM7=2);22,10;IF(AND(AD7=72;AM7=2);23,95;IF(AND(AD7=73;AM7=2);25,65;IF(AND(AD7=74;AM7=2);90,38;IF(AND(AD7>=75;AD7<=78;AM7=2);97,92;

IF(AND(AD7=70;AM7=3);32,64;IF(AND(AD7=71;AM7=3);34,15;IF(AND(AD7=72;AM7=3);36,43;IF(AND(AD7=73;AM7=3);135,49;IF(AND(AD7=74;AM7=3);139,23;IF(AND(AD7>=75;AD7<=77;AM7=3);143,39;

IF(AND(AD7=70;AM7=4);43,52;IF(AND(AD7=71;AM7=4);46,20;IF(AND(AD7=72;AM7=4);161,56;IF(AND(AD7=73;AM7=4);173,52;IF(AND(AD7=74;AM7=4);178,31;IF(AND(AD7>=75;AD7<=76;AM7=4);190,40;

IF(AND(AD7=70;AM7=5);57,40;IF(AND(AD7=71;AM7=5);191,79;IF(AND(AD7=72;AM7=5);199,17;IF(AND(AD7=73;AM7=5);213,93;IF(AND(AD7=74;AM7=5);219,83;IF(AND(AD7=75;AM7=5);238,00;

IF(AND(AD7=70;AM7=6);217,87;IF(AND(AD7=71;AM7=6);221,28;IF(AND(AD7=72;AM7=6);229,79;IF(AND(AD7=73;AM7=6);246,81;IF(AND(AD7=74;AM7=6);253,62;

IF(AND(AD7=70;AM7=7);243,40;IF(AND(AD7=71;AM7=7);247,20;IF(AND(AD7=72;AM7=7);256,71;IF(AND(AD7=73;AM7=7);275,72;

IF(AND(AD7=70;AM7=8);268,58;IF(AND(AD7=71;AM7=8);272,78;IF(AND(AD7=72;AM7=8);283,27;

IF(AND(AD7=70;AM7=9);291,66;IF(AND(AD7=71;AM7=9);296,22;

IF(AND(AD7=70;AM7=10);303,90;FALSE)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

 

I appreciate your help in advance!

 

Thank you,

 

NB: sorry if my english bad

 

2 Replies
I would suggest to use a lookup table then use XLOOKUP() or FILTER() function which will be too simplified.
so i need to spreed the age?ok thank you for the advice i will try xloopup fir the formula