Nesting IF Statement Problem

T

TooZippy

Board Regular
Joined
Dec 30, 2018
Messages
70
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...

Code: 
[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
​
 
Sort by date Sort by votes
sandy666 said:
your formula is still not correct
Click to expand...

Code: 
[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]

Try it now. I have added a space before and after every "<" and ">" in the formula.

TooZippy
 
Upvote 0

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
ok,
so you can try take five IFs , wrap it with IF and if these five IFs = 0 (FALSE) add next five IFs

eg. IF(fiveIFs=0, nextfiveIFs,fiveIFs)

it will be a monster formula so you need to check limitations for Excel 2003
 
Last edited:
Upvote 0
sandy666 said:
ok,
so you can try take five IFs , wrap it with IF and if these five IFs = 0 (FALSE) add next five IFs

eg. IF(fiveIFs=0, nextfiveIFs,fiveIFs)

it will be a monster formula so you need to check limitations for Excel 2003
Click to expand...

Thank you for your help.

TooZippy :eek:
 
Upvote 0
you are welcome

btw. IMHO it is easier to manage such a formula in this form but it's up2u

Code: 
			=IF(
				$AG3 < VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0),
						$AF3*'Enter Tax Data'!$C$16,
			IF(
				AND(
					$A3=$A2,
					(AG3-AF3) < VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0),
					$AG3 < VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,0)
					),
						((VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0)-($AG3-$AF3))*'Enter Tax Data'!$C$16)+(($AG3-VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0))*'Enter Tax Data'!$C$17),
			IF(
				AND(
					$A3=$A2,
					($AG3-$AF3) > VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0),
					$AG3 > VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0),
					$AG3 < VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,0)
					),
						$AF3*'Enter Tax Data'!$C$17,
			IF(
				AND(
					$A3=$A2,
					($AG3-$AF3) > VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0),
					$AG3 < VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,0)
					),
						(VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,0)-($AG3-$AF3))*'Enter Tax Data'!$C$17,
			IF(
				AND(
					$A3 < > $A2,
					VLOOKUP($A3,$BD$3:$BS$15,16,0) < VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0),
					$AG3 > VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0)
					),
						((VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0)-VLOOKUP($A3,$BD$3:$BS$15,16,0))*'Enter Tax Data'!$C$16)+(($AG3-VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0))*'Enter Tax Data'!$C$17),
			IF(
				AND(
					$A3 < > $A2,
					VLOOKUP($A3,$BD$3:$BS$15,16,0) > VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0),
					$AG3 < VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,0)
					),
						$AF3*'Enter Tax Data'!$C$17,
			IF(
				AND(
					$A3 < > $A2,
					$AG3 > VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0),
					$AG3 < VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,0)
					),
						$AF3*'Enter Tax Data'!$C$17,
			IF(
				AND(
					$A3 < > $A2,
					$AG3 > VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0),
					$AG3 > VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,0),
					VLOOKUP($A3,$BD$3:$BS$15,16,0) < VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,0)
					),
						(VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,0)-VLOOKUP($A3,$BD$3:$BS$15,16,0))*'Enter Tax Data'!$C$17,
			IF(
				AND(
					$A3=$A2,
					$AG3 > VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,0),
					($AG3-$AF3) > VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0),
					($AG3-$AF3) < VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,0)
					),
						((VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,0)-($AG3-$AF3))*'Enter Tax Data'!$C$17),
			IF(
				AND(
					$A3=$A2,
					VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0) > ($AG3-$AF3),
					$AG3 > VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,0)
					),
						((VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0)-($AG3-$AF3))*'Enter Tax Data'!$C$16)+(($AG3-(VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0)))*'Enter Tax Data'!$C$17),
				"")
				)))))))))
 
Last edited:
Upvote 0
sandy666 said:
you are welcome

btw. IMHO it is easier to manage such a formula in this form but it's up2u

Code: 
            =IF(
                $AG3 < VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0),
                        $AF3*'Enter Tax Data'!$C$16,
            IF(
                AND(
                    $A3=$A2,
                    (AG3-AF3) < VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0),
                    $AG3 < VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,0)
                    ),
                        ((VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0)-($AG3-$AF3))*'Enter Tax Data'!$C$16)+(($AG3-VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0))*'Enter Tax Data'!$C$17),
            IF(
                AND(
                    $A3=$A2,
                    ($AG3-$AF3) > VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0),
                    $AG3 > VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0),
                    $AG3 < VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,0)
                    ),
                        $AF3*'Enter Tax Data'!$C$17,
            IF(
                AND(
                    $A3=$A2,
                    ($AG3-$AF3) > VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0),
                    $AG3 < VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,0)
                    ),
                        (VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,0)-($AG3-$AF3))*'Enter Tax Data'!$C$17,
            IF(
                AND(
                    $A3 < > $A2,
                    VLOOKUP($A3,$BD$3:$BS$15,16,0) < VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0),
                    $AG3 > VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0)
                    ),
                        ((VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0)-VLOOKUP($A3,$BD$3:$BS$15,16,0))*'Enter Tax Data'!$C$16)+(($AG3-VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0))*'Enter Tax Data'!$C$17),
            IF(
                AND(
                    $A3 < > $A2,
                    VLOOKUP($A3,$BD$3:$BS$15,16,0) > VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0),
                    $AG3 < VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,0)
                    ),
                        $AF3*'Enter Tax Data'!$C$17,
            IF(
                AND(
                    $A3 < > $A2,
                    $AG3 > VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0),
                    $AG3 < VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,0)
                    ),
                        $AF3*'Enter Tax Data'!$C$17,
            IF(
                AND(
                    $A3 < > $A2,
                    $AG3 > VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0),
                    $AG3 > VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,0),
                    VLOOKUP($A3,$BD$3:$BS$15,16,0) < VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,0)
                    ),
                        (VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,0)-VLOOKUP($A3,$BD$3:$BS$15,16,0))*'Enter Tax Data'!$C$17,
            IF(
                AND(
                    $A3=$A2,
                    $AG3 > VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,0),
                    ($AG3-$AF3) > VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0),
                    ($AG3-$AF3) < VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,0)
                    ),
                        ((VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,0)-($AG3-$AF3))*'Enter Tax Data'!$C$17),
            IF(
                AND(
                    $A3=$A2,
                    VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0) > ($AG3-$AF3),
                    $AG3 > VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,11,0)
                    ),
                        ((VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0)-($AG3-$AF3))*'Enter Tax Data'!$C$16)+(($AG3-(VLOOKUP($A3,EmpeeEmpor!$A$3:$M$15,10,0)))*'Enter Tax Data'!$C$17),
                "")
                )))))))))
Click to expand...

Thank you for the suggestion, but I don't think it would solve the problem. It is still nesting 10 if statements and the max that I can use in Excel 97-2003 is 6.

TooZippy ;)
 
Upvote 0
i think there is 6 IFs
five in logical_condition then five in TRUE then five in FALSE. they're working independently.
but of course it's up to you what you will do :)

if you afraid about too many IFs you can use helper cells
A1: first five IFs
B1: second five IFs
then IF(A1=0,B1,A1)

there is no Click&Go solution ;)
 
Last edited:
Upvote 0
sandy666 said:
i think there is 6 IFs
five in logical_condition then five in TRUE then five in FALSE. they're working independently.
but of course it's up to you what you will do :)

if you afraid about too many IFs you can use helper cells
A1: first five IFs
B1: second five IFs
then IF(A1=0,B1,A1)

there is no Click&Go solution ;)
Click to expand...

I copied and pasted the above formula into the 97-2003 version of my spreadsheet and took out all of the spaces. I got this message..."This formula uses more levels of nesting that you can use in the current file format." Thank you for trying.

TooZippy :eek:
 
Upvote 0
formula from post#14 isn't a solution. I made it to manage your original formula easier, nothing more
 
Upvote 0
TooZippy said:
I copied and pasted the above formula into the 97-2003 version of my spreadsheet and took out all of the spaces. I got this message..."This formula uses more levels of nesting that you can use in the current file format." Thank you for trying.

TooZippy :eek:
Click to expand...

Is there a way to shorten the formula or is my only way to solve this is to break up the formula in two parts and put the two parts in two different columns???

Thank you,

TooZippy
 
Upvote 0
I don't know.
I don't have Ex2003 and I don't have the data so I can't test it
maybe someone else will give any solution

have a nice day
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Formula is changing after getting new data?
Replies
7
Views
332
JamesRo
J
P
  • Question
Offset Processes
Replies
7
Views
282
SanjayGMusafir
SanjayGMusafir
M
  • Question
Running unique count of occurrence on table based on date
Replies
0
Views
176
Minx_Minxy
M
J
  • Solved
VLookup Struggles
Replies
7
Views
298
Peter_SSs
Peter_SSs
J
  • Question
Take Excel Worksheet, Divide into Worksheets/Workbooks by Employee/Card #, Email via Gmail to Employee for Input
Replies
9
Views
606
jennifer7i884
J

Forum statistics

Threads
1,215,332
Messages
6,124,314
Members
449,153
Latest member
JazzSingerNL

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top