IF function has more than 64 levels of nesting

[AntonA]

Hello,

I am creating a tool in excel using the IF functions. I am sure there is an easier way of doing this but I have no idea how. I just got through creating the formula but I am getting an error message that indicates I have exceeding the 64 levels of nesting. I have looked this issue up in the forums but I cannot find an answer that helps me resolve my issue. Here is the formula:

=IFERROR(IF($AK166=1,"Shipped",IF($AK166=3,"Shipping Que",IF($AJ166=2,"Packing Inprocess",IF($AJ166=1,"Packing Finish",IF($AJ166=3,"Packing Que",IF($AI166=1,"Perfection Review Finish",IF($AI166=2,"Perfection Review Inprocess",IF($AI166=3,"Perfection Review Que",IF($AH166=2,"Cleaning Inprocess",IF($AH166=1,"Cleaning Finish",IF($AH166=3,"Cleaning Que",IF($AG166=2,"Final Dim Check & Final Inspect Inprocess",IF($AG166=1,"Final Dim Check, Fin. Inspect & Rework Finish",IF($AG166=3,"Final Dim Check & Final Inspect Que",IF($AF166=2,"Final Assy / Final Setting Inprocess",IF($AF166=1,"Final Assy / Final Setting Finish",IF($AF166=3,"Final Assy / Final Setting Que",IF($AE166=2,"Final Inspect After Coating Inprocess",IF($AE166=1,"Final Inspect After Coating Finish",IF($AE166=3,"Final Inspect After Coating Que",IF($AD166=1,"HT After Coating / Slurry /Honey Comb Finish",IF($AD166=2,"HT After Coating / Slurry /Honey Comb Inprocess",IF($AD166=3,"HT After Coating / Slurry /Honey Comb Que",IF($AC166=2,"Coating Inprocess",IF($AC166=1,"Coating Finish",IF($AC166=3,"Coating Que",IF($AB166=1,"Final Blast Before Coating Finish",IF($AB166=2,"Final Blast Before Coating Inprocess",IF($AB166=3,"Final Blast Before Coating Que",IF($AA166=2,"Post. HT Inprocess",IF($AA166=1,"Post. HT Finish",IF($AA166=3,"Post. HT Que",IF($Z166=2,"Touch up Inprocess",IF($Z166=1,"Touch up Finish",IF($Z166=3,"Touch up Que",IF($Y166=2,"Fin. NDT & WCRT Inprocess",IF($Y166=1,"Fin. NDT & WCRT Finish",IF($Y166=3,"Fin. NDT & WCRT Que",IF(X166=2,"Machining Inprocess",IF($X166=1,"Machining Finish",IF($X166=3,"Machining Que",IF($W166=2,"Fitting Crs./Roudness & Dim Check Inprocess",IF($W166=1,"Fitting Crs./Roudness & Dim Check Finish",IF($W166=3,"Fitting Crs./Roudness & Dim Check Que",IF($V166=2,"Weld Repair Inprocess",IF($V166=1,"Weld Repair Finish",IF($V166=3,"Weld Repair Que",IF($U166=2,"Inc. NDT & WCRT Inprocess",IF($U166=1,"Inc. NDT & WCRT Finish",IF($U166=3,"Inc. NDT & WCRT Que",IF($T166=2,"Pre. HT Inprocess",IF($T166=1,"Pre. HT Finish",IF($T166=3,"Pre. HT Que",IF($S166=2,"Inc. Blast Inprocess",IF($S166=1,"Inc Blast Finish",IF($S166=3,"Inc Blast Que",IF($R166=1,"Disassy Finish",IF($R166=2,"Disassy Inprocess",IF($R166=3,"Disassy Que",IF($Q166=1,"Receiving & Dim Inspect Finish",IF($Q166=2,"Receiving & Dim Inspect Inprocess",IF($Q166=3,"Receiving & Dim Inspect Que",IF($P166=1,"Arrival",IF($P166=3,"Arrival Que","")))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))),"")

I attached a spreadsheet as well. Any suggestions as to whether or not there is an easier way to put this together would be appreciated.

 

[jmpatrick]

Hello,

I am creating a tool in excel using the IF functions. I am sure there is an easier way of doing this but I have no idea how. I just got through creating the formula but I am getting an error message that indicates I have exceeding the 64 levels of nesting. I have looked this issue up in the forums but I cannot find an answer that helps me resolve my issue. Here is the formula:

=IFERROR(IF($AK166=1,"Shipped",IF($AK166=3,"Shipping Que",IF($AJ166=2,"Packing Inprocess",IF($AJ166=1,"Packing Finish",IF($AJ166=3,"Packing Que",IF($AI166=1,"Perfection Review Finish",IF($AI166=2,"Perfection Review Inprocess",IF($AI166=3,"Perfection Review Que",IF($AH166=2,"Cleaning Inprocess",IF($AH166=1,"Cleaning Finish",IF($AH166=3,"Cleaning Que",IF($AG166=2,"Final Dim Check & Final Inspect Inprocess",IF($AG166=1,"Final Dim Check, Fin. Inspect & Rework Finish",IF($AG166=3,"Final Dim Check & Final Inspect Que",IF($AF166=2,"Final Assy / Final Setting Inprocess",IF($AF166=1,"Final Assy / Final Setting Finish",IF($AF166=3,"Final Assy / Final Setting Que",IF($AE166=2,"Final Inspect After Coating Inprocess",IF($AE166=1,"Final Inspect After Coating Finish",IF($AE166=3,"Final Inspect After Coating Que",IF($AD166=1,"HT After Coating / Slurry /Honey Comb Finish",IF($AD166=2,"HT After Coating / Slurry /Honey Comb Inprocess",IF($AD166=3,"HT After Coating / Slurry /Honey Comb Que",IF($AC166=2,"Coating Inprocess",IF($AC166=1,"Coating Finish",IF($AC166=3,"Coating Que",IF($AB166=1,"Final Blast Before Coating Finish",IF($AB166=2,"Final Blast Before Coating Inprocess",IF($AB166=3,"Final Blast Before Coating Que",IF($AA166=2,"Post. HT Inprocess",IF($AA166=1,"Post. HT Finish",IF($AA166=3,"Post. HT Que",IF($Z166=2,"Touch up Inprocess",IF($Z166=1,"Touch up Finish",IF($Z166=3,"Touch up Que",IF($Y166=2,"Fin. NDT & WCRT Inprocess",IF($Y166=1,"Fin. NDT & WCRT Finish",IF($Y166=3,"Fin. NDT & WCRT Que",IF(X166=2,"Machining Inprocess",IF($X166=1,"Machining Finish",IF($X166=3,"Machining Que",IF($W166=2,"Fitting Crs./Roudness & Dim Check Inprocess",IF($W166=1,"Fitting Crs./Roudness & Dim Check Finish",IF($W166=3,"Fitting Crs./Roudness & Dim Check Que",IF($V166=2,"Weld Repair Inprocess",IF($V166=1,"Weld Repair Finish",IF($V166=3,"Weld Repair Que",IF($U166=2,"Inc. NDT & WCRT Inprocess",IF($U166=1,"Inc. NDT & WCRT Finish",IF($U166=3,"Inc. NDT & WCRT Que",IF($T166=2,"Pre. HT Inprocess",IF($T166=1,"Pre. HT Finish",IF($T166=3,"Pre. HT Que",IF($S166=2,"Inc. Blast Inprocess",IF($S166=1,"Inc Blast Finish",IF($S166=3,"Inc Blast Que",IF($R166=1,"Disassy Finish",IF($R166=2,"Disassy Inprocess",IF($R166=3,"Disassy Que",IF($Q166=1,"Receiving & Dim Inspect Finish",IF($Q166=2,"Receiving & Dim Inspect Inprocess",IF($Q166=3,"Receiving & Dim Inspect Que",IF($P166=1,"Arrival",IF($P166=3,"Arrival Que","")))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))),"")

I attached a spreadsheet as well. Any suggestions as to whether or not there is an easier way to put this together would be appreciated.

I can't help you with an exact answer but I can tell you I needed to do a very similar thing. Here's how I solved it (with a lot of great help from this forum):

How can I simplify this HYPERLINK formula?

I have this formula that creates hyperlinks based on the contents in C8. This works fine but it looks rather ugly. It seems like the ))))) characters look clunky. Is there a more elegant way of doing this? =IF(COUNTIF($C8,"*1072*"),HYPERLINK("mailto:chadd.willson@blahblahblah","Chadd...

www.mrexcel.com

Hope it help!

 

[Eric W]

It looks like you're first checking column AK for a 1 or a 3. If not found, check AJ for 1,2,3, if not found, keep moving to the left until column P. Is that right? If AK doesn't have a 1 or 3, what else could it have? A 2? An empty cell? A text value? Something else?

 

[gravanoc]

Hello,

I am creating a tool in excel using the IF functions. I am sure there is an easier way of doing this but I have no idea how. I just got through creating the formula but I am getting an error message that indicates I have exceeding the 64 levels of nesting. I have looked this issue up in the forums but I cannot find an answer that helps me resolve my issue. Here is the formula:

=IFERROR(IF($AK166=1,"Shipped",IF($AK166=3,"Shipping Que",IF($AJ166=2,"Packing Inprocess",IF($AJ166=1,"Packing Finish",IF($AJ166=3,"Packing Que",IF($AI166=1,"Perfection Review Finish",IF($AI166=2,"Perfection Review Inprocess",IF($AI166=3,"Perfection Review Que",IF($AH166=2,"Cleaning Inprocess",IF($AH166=1,"Cleaning Finish",IF($AH166=3,"Cleaning Que",IF($AG166=2,"Final Dim Check & Final Inspect Inprocess",IF($AG166=1,"Final Dim Check, Fin. Inspect & Rework Finish",IF($AG166=3,"Final Dim Check & Final Inspect Que",IF($AF166=2,"Final Assy / Final Setting Inprocess",IF($AF166=1,"Final Assy / Final Setting Finish",IF($AF166=3,"Final Assy / Final Setting Que",IF($AE166=2,"Final Inspect After Coating Inprocess",IF($AE166=1,"Final Inspect After Coating Finish",IF($AE166=3,"Final Inspect After Coating Que",IF($AD166=1,"HT After Coating / Slurry /Honey Comb Finish",IF($AD166=2,"HT After Coating / Slurry /Honey Comb Inprocess",IF($AD166=3,"HT After Coating / Slurry /Honey Comb Que",IF($AC166=2,"Coating Inprocess",IF($AC166=1,"Coating Finish",IF($AC166=3,"Coating Que",IF($AB166=1,"Final Blast Before Coating Finish",IF($AB166=2,"Final Blast Before Coating Inprocess",IF($AB166=3,"Final Blast Before Coating Que",IF($AA166=2,"Post. HT Inprocess",IF($AA166=1,"Post. HT Finish",IF($AA166=3,"Post. HT Que",IF($Z166=2,"Touch up Inprocess",IF($Z166=1,"Touch up Finish",IF($Z166=3,"Touch up Que",IF($Y166=2,"Fin. NDT & WCRT Inprocess",IF($Y166=1,"Fin. NDT & WCRT Finish",IF($Y166=3,"Fin. NDT & WCRT Que",IF(X166=2,"Machining Inprocess",IF($X166=1,"Machining Finish",IF($X166=3,"Machining Que",IF($W166=2,"Fitting Crs./Roudness & Dim Check Inprocess",IF($W166=1,"Fitting Crs./Roudness & Dim Check Finish",IF($W166=3,"Fitting Crs./Roudness & Dim Check Que",IF($V166=2,"Weld Repair Inprocess",IF($V166=1,"Weld Repair Finish",IF($V166=3,"Weld Repair Que",IF($U166=2,"Inc. NDT & WCRT Inprocess",IF($U166=1,"Inc. NDT & WCRT Finish",IF($U166=3,"Inc. NDT & WCRT Que",IF($T166=2,"Pre. HT Inprocess",IF($T166=1,"Pre. HT Finish",IF($T166=3,"Pre. HT Que",IF($S166=2,"Inc. Blast Inprocess",IF($S166=1,"Inc Blast Finish",IF($S166=3,"Inc Blast Que",IF($R166=1,"Disassy Finish",IF($R166=2,"Disassy Inprocess",IF($R166=3,"Disassy Que",IF($Q166=1,"Receiving & Dim Inspect Finish",IF($Q166=2,"Receiving & Dim Inspect Inprocess",IF($Q166=3,"Receiving & Dim Inspect Que",IF($P166=1,"Arrival",IF($P166=3,"Arrival Que","")))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))),"")

I attached a spreadsheet as well. Any suggestions as to whether or not there is an easier way to put this together would be appreciated.

I remembered reading this post by Brad Yundt a while ago where he referenced such a situation with nested if statements. Let me know if his solution works for you.
Brad's explanation

 

[Alex Blakenburg]

See if this works for you in principle:-
(copy it into M1)
If you need shorter names in the Status column either:-
• Add another row of headings with the shorter name you want to use (you can hide the row)
• Have a mapping table from the current headings to shorter headings.

20210824 Index Match Last Numeric Value.xlsx
	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
1	Last Status	Date Last Status	Prog%	Arrival	Col2	Col3	Col4	Col5	Col6	Col7	Col8	Col9	Col10	Col11	Col12	Col13	Col14	HT After Coating	Col16	Col17	Col18	Cleaning	Col20	Packaging	Shipped
2	Shipped In Progress																	N/A						1	2
3	Shipped Finished																	N/A						2	1
4	Cleaning Queu																	N/A	1	1	1	3
5
Sheet1

Cell Formulas
Range	Formula
M2:M4	M2	=INDEX($P$1:$AK$1,MATCH(1E+100,$P2:$AK2,1)) & " " & CHOOSE(INDEX($P2:$AK2,MATCH(1E+100,$P2:$AK2,1)),"Finished","In Progress","Queu")

 

[johnnyL]

I think vlookup would be the best approach.

 

[AntonA]

Hi Alex,

Thanks for your help, that formula is work well on my excel project

Thanks a lot??

 

[Eric W]

This is what I pictured:

Book4
	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
165				Step 1	Step 2	Step 3	Step 4	Step 5	Step 6	Step 7	Step 8	Step 9	Step 10	Step 11	Step 12	Step 13	Step 14	Step 15	Step 16	Step 17	Step 18	Step 19	Step 20	Step 21
166				1	2	3	1	2	3	4		1		3		3	1	2	3	1		2
167
168	Latest Description:	Cleaning Inprocess
169
170	Code	Description
171	AK1	Shipped
172	AK3	Shipping Queue
173	AJ1	Packing finish
174	AJ2	Packing Inprocess
175	AJ3	Packing Queue
176	AI1	Perfection Review Finish
177	AI2	Perfection Review Inprocess
178	AI3	Perfection Review Queue
179	AH1	Cleaning Finish
180	AH2	Cleaning Inprocess
181	AH3	Cleaning Queue
182	P1	Arrival
183	P3	Arrival Queue
Sheet6

Cell Formulas
Range	Formula
N168	N168	=VLOOKUP(ADDRESS(LOOKUP(9^9,P166:AJ166),LOOKUP(9^9,P166:AJ166,COLUMN(P166:AJ166)),4),$M$171:$N$183,2,0)

Each row represents the process for a piece of work. As the process continues, a code is added to columns going to the right. So this formula looks for the last used column, gets the column letter, and the value, and combines them into a code. Then this code can be used in in standard VLOOKUP table. In this example, the last used column is AH, with a value of 2, so the code is AH2. Then the VLOOKUP finds that in the table and returns the right description. Changing the table is easy.

 

[Alex Blakenburg]

Hi Alex,

Thanks for your help, that formula is work well on my excel project

Thanks a lot??

Thanks for the feedback. Glad we could help.