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
​

 

[sandy666]

could you explain what does that mean?

IF($AG3VLOOKUP( ?
syntax: =IF(logical_test, value_if_true, value_if_false)

and some more like this....

maybe you tried IFS()
syntax: IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3],…)

 

[Fluff]

The board has destroyed the formula. @TooZippy
Try posting your formula again but ensure that you put spaces either side of < & > symbols.
Otherwise they are treated as HTML code.

 

[TooZippy]

could you explain what does that mean?

IF($AG3VLOOKUP( ?
syntax: =IF(logical_test, value_if_true, value_if_false)

and some more like this....

maybe you tried IFS()
syntax: IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3],…)

Sorry...Let me try pasting the formula again.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=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),""))))))))))[/FONT]

I think that the "<" was missing in the first attempt.

TooZippy

 

[TooZippy]

When I clicked submit, Mr Excel removed the "<" from the formula. I don't know why???

TooZippy

 

[Fluff]

Did you read post#3?

 

[TooZippy]

Did you read post#3 ?

Yes, I did read post #3 . I don't understand why Mr. Excel removed the "<" from the formula. I tried repasting the formula a second time and the "<" was there after I pasted and before I posted it. It then disappeared after I posted the formula???

TooZippy

 

[Fluff]

As I said the software interprets them as HTML code, so you need to add spaces before & after both < & > symbols

 

[TooZippy]

Yes, I did read post [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3 [/URL] . I don't understand why Mr. Excel removed the "<" from the formula. I tried repasting the formula a second time and the "<" was there after I pasted and before I posted it. It then disappeared after I posted the formula???

TooZippy

One more try...

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=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),""))))))))))[/FONT]

Oops!!! Guess I looked at the wrong #3 .

TooZippy

 

[sandy666]

your formula is still not correct