Nesting IF Statement Problem

[TooZippy]

I created a series of IF statements in Excel 365 that include VLOOKUP statements. In the Excel 365 version I nested 10 IF statements together and the formula works. When I tried to save the file as an Excel 97-2003 version the formula was erased. In this older version, I think the maximum IF statements that I can nest together is 6 thereby making the problem. Is there a way around this to make the formula shorter so that it would work in Excel 97-2003? Here is the formula...

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][SIZE=3][COLOR=#001000]=IF($AG3<VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,FALSE),$AF3*'Enter Tax Data'!$C$16,IF(AND($A3=$A2,(AG3-AF3)<VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,FALSE),$AG3<VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,FALSE)),((VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,FALSE)-($AG3-$AF3))*'Enter Tax Data'!$C$16)+(($AG3-VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,FALSE))*'Enter Tax Data'!$C$17),IF(AND($A3=$A2,($AG3-$AF3)>VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,FALSE),$AG3>VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,FALSE),$AG3<VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,FALSE)),$AF3*'Enter Tax Data'!$C$17,IF(AND($A3=$A2,($AG3-$AF3)>VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,FALSE),$AG3<VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,FALSE)),(VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,FALSE)-($AG3-$AF3))*'Enter Tax Data'!$C$17,IF(AND($A3<>$A2,VLOOKUP($A3,$BD$3:$BS$15,16,FALSE)<VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,FALSE),$AG3>VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,FALSE)),((VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,FALSE)-VLOOKUP($A3,$BD$3:$BS$15,16,FALSE))*'Enter Tax Data'!$C$16)+(($AG3-VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,FALSE))*'Enter Tax Data'!$C$17),IF(AND($A3<>$A2,VLOOKUP($A3,$BD$3:$BS$15,16,FALSE)>VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,FALSE),$AG3<VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,FALSE)),$AF3*'Enter Tax Data'!$C$17,IF(AND($A3<>$A2,$AG3>VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,FALSE),$AG3<VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,FALSE)),$AF3*'Enter Tax Data'!$C$17,IF(AND($A3<>$A2,$AG3>VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,FALSE),$AG3>VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,FALSE),VLOOKUP($A3,$BD$3:$BS$15,16,FALSE)<VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,FALSE)),(VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,FALSE)-VLOOKUP($A3,$BD$3:$BS$15,16,FALSE))*'Enter Tax Data'!$C$17,IF(AND($A3=$A2,$AG3>VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,FALSE),($AG3-$AF3)>VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,FALSE),($AG3-$AF3)<VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,FALSE)),((VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,FALSE)-($AG3-$AF3))*'Enter Tax Data'!$C$17),IF(AND($A3=$A2,VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,FALSE)>($AG3-$AF3),$AG3>VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,FALSE)),((VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,FALSE)-($AG3-$AF3))*'Enter Tax Data'!$C$16)+(($AG3-(VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,FALSE)))*'Enter Tax Data'!$C$17),""))))))))))[/COLOR][/SIZE][/FONT]

Thank you,

TooZippy
​

 

[ClaireS]

May I point you at this link to OzGrid: it seems to be doing more or less what you need. It uses range names as formula constants, and you will need to adapt and fiddle with it, but I think it looks as though it's pointing in the right direction...