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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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 :eeek:
 
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 :eeek:
 
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 :eeek:
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

M
  • Solved
XLOOKUP help (maybe), not sure what function to use
Replies
4
Views
252
MeaclH
M
M
  • Question Question
VLOOKUP?
Replies
1
Views
212
MeaclH
M
K
  • Solved
COUNTIFS formula.
2
Replies
14
Views
604
Kayslover
K
HansM
  • Question Question
Looking for a specific number in a cell that can have multiple numbers separated by a comma (Using Mac Excel)
Replies
5
Views
295
Jeffrey Mahoney
Jeffrey Mahoney
E
  • Solved
extend Excel formula to filter based on a period
Replies
5
Views
274
smozgur
smozgur

Forum statistics

Threads
1,223,889
Messages
6,175,223
Members
452,620
Latest member
dsubash

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