Nestling: The specified formula cannot be entered because it uses more than 64 levels of nestling

[Beunite]

Hi there,

I'm new to the community. Please help me to follow the right procedures.

I have a problem with nestling and I need your help.

Here's the formula, it's a simple ifand formula. The formula works like a beauty until the last city (Yangon). Then the error message pops up.

The specified formula cannot be entered because it uses more than 64 levels of nestling

It consists of the cities, Bangkok, Phuket, KohSamui, etc. and the duration half-day, full-day, two-day, etc. so it pretty much repeats itself from the beginning up until the with all combinations of cities and duration. I will have more cities to add in the future, up to 50.

I hope it's not too long and doesn't cause confusion:

Thank you for helping me to crack this nut.

Kind regards

This is the formula that works up until Yangon:

=IF(AND($C4="Bangkok",$A4="shorter than half-day"),Accommodation!$B$2,IF(AND($C4="Bangkok",$A4="half-day"),Accommodation!$B$3,IF(AND($C4="Bangkok",$A4="full-day"),Accommodation!$B$4,IF(AND($C4="Bangkok",$A4="two-day"),Accommodation!$B$5,IF(AND($C4="Bangkok",$A4="longer than two days"),Accommodation!$B$6,IF(AND($C4="Phuket",$A4="shorter than half-day"),Accommodation!$C$2,IF(AND($C4="Phuket",$A4="half-day"),Accommodation!$C$3,IF(AND($C4="Phuket",$A4="full-day"),Accommodation!$C4,IF(AND($C4="Phuket",$A4="two-day"),Accommodation!$C$5,IF(AND($C4="Phuket",$A4="longer than two days"),Accommodation!$C$6,IF(AND($C4="Koh Samui",$A4="shorter than half-day"),Accommodation!$D$2,IF(AND($C4="Koh Samui",$A4="half-day"),Accommodation!$D$3,IF(AND($C4="Koh Samui",$A4="full-day"),Accommodation!$D$4,IF(AND($C4="Koh Samui",$A4="two-day"),Accommodation!$D$5,IF(AND($C4="Koh Samui",$A4="longer than two days"),Accommodation!$D$6,IF(AND($C4="Hua Hin",$A4="shorter than half-day"),Accommodation!$E$2,IF(AND($C4="Hua Hin",$A4="half-day"),Accommodation!$E$3,IF(AND($C4="Hua Hin",$A4="full-day"),Accommodation!$E$4,IF(AND($C4="Hua Hin",$A4="two-day"),Accommodation!$E$5,IF(AND($C4="Hua Hin",$A4="longer than two days"),Accommodation!$E$6,IF(AND($C4="Pattaya",$A4="shorter than half-day"),Accommodation!$F$2,IF(AND($C4="Pattaya",$A4="half-day"),Accommodation!$F$3,IF(AND($C4="Pattaya",$A4="full-day"),Accommodation!$F$4,IF(AND($C4="Pattaya",$A4="two-day"),Accommodation!$F$5,IF(AND($C4="Pattaya",$A4="longer than two days"),Accommodation!$F$6,IF(AND($C4="Chiang Mai",$A4="shorter than half-day"),Accommodation!$G$2,IF(AND($C4="Chiang Mai",$A4="half-day"),Accommodation!$G$3,IF(AND($C4="Chiang Mai",$A4="full-day"),Accommodation!$G$4,IF(AND($C4="Chiang Mai",$A4="two-day"),Accommodation!$G$5,IF(AND($C4="Chiang Mai",$A4="longer than two days"),Accommodation!$G$6, IF(AND($C4="Singapore",$A4="shorter than half-day"),Accommodation!$H$2,IF(AND($C4="Singapore",$A4="half-day"),Accommodation!$H$3,IF(AND($C4="Singapore",$A4="full-day"),Accommodation!$H$4,IF(AND($C4="Singapore",$A4="two-day"),Accommodation!$H$5,IF(AND($C4="Singapore",$A4="longer than two days"),Accommodation!$H$6, IF(AND($C4="Hong Kong",$A4="shorter than half-day"),Accommodation!$I$2,IF(AND($C4="Hong Kong",$A4="half-day"),Accommodation!$I$3,IF(AND($C4="Hong Kong",$A4="full-day"),Accommodation!$I$4,IF(AND($C4="Hong Kong",$A4="two-day"),Accommodation!$I$5,IF(AND($C4="Hong Kong",$A4="longer than two days"),Accommodation!$I$6,IF(AND($C4="Kuala Lumpur",$A4="shorter than half-day"),Accommodation!$J$2,IF(AND($C4="Kuala Lumpur",$A4="half-day"),Accommodation!$J$3,IF(AND($C4="Kuala Lumpur",$A4="full-day"),Accommodation!$J$4,IF(AND($C4="Kuala Lumpur",$A4="two-day"),Accommodation!$J$5,IF(AND($C4="Kuala Lumpur",$A4="longer than two days"),Accommodation!$J$6,IF(AND($C4="Jakarta",$A4="shorter than half-day"),Accommodation!$K$2,IF(AND($C4="Jakarta",$A4="half-day"),Accommodation!$K$3,IF(AND($C4="Jakarta",$A4="full-day"),Accommodation!$K$4,IF(AND($C4="Jakarta",$A4="two-day"),Accommodation!$K$5,IF(AND($C4="Jakarta",$A4="longer than two days"),Accommodation!$K$6,IF(AND($C4="Siem Reap",$A4="shorter than half-day"),Accommodation!$L$2,IF(AND($C4="Siem Reap",$A4="half-day"),Accommodation!$L$3,IF(AND($C4="Siem Reap",$A4="full-day"),Accommodation!$L$4,IF(AND($C4="Siem Reap",$A4="two-day"),Accommodation!$L$5,IF(AND($C4="Siem Reap",$A4="longer than two days"),Accommodation!$L$6,IF(AND($C4="Yangon",$A4="shorter than half-day"),Accommodation!$M$2,IF(AND($C4="Yangon",$A4="half-day"),Accommodation!$M$3, IF(AND($C4="Yangon",$A4="full-day"),Accommodation!$M$4,IF(AND($C4="Yangon",$A4="two-day"),Accommodation!$M$5,IF(AND($C4="Yangon",$A4="longer than two days"),Accommodation!$M$6 ))))))))))))))))))))))))))))))))))))))))))))))))))))))) )))))

This is the last part of the formula that doesn't work when added to the previous one:

,IF(AND($C4="Colombo",$A4="shorter than half-day"),'ACCOMMODATION'!$N$2,IF(AND($C4="Colombo",$A4="half-day"),'ACCOMMODATION'!$N$3,IF(AND($C4="Colombo",$A4="full-day"),'ACCOMMODATION'!$N$4,IF(AND($C4="Colombo",$A4="two-day"),'ACCOMMODATION'!$N$5,IF(AND($C4="Colombo",$A4="longer than two days"),'ACCOMMODATION'!$N$6 )))))


This is the entire formula that unfortunately doesn't work:

=IF(AND($C4="Bangkok",$A4="shorter than half-day"),Accommodation!$B$2,IF(AND($C4="Bangkok",$A4="half-day"),Accommodation!$B$3,IF(AND($C4="Bangkok",$A4="full-day"),Accommodation!$B$4,IF(AND($C4="Bangkok",$A4="two-day"),Accommodation!$B$5,IF(AND($C4="Bangkok",$A4="longer than two days"),Accommodation!$B$6,IF(AND($C4="Phuket",$A4="shorter than half-day"),Accommodation!$C$2,IF(AND($C4="Phuket",$A4="half-day"),Accommodation!$C$3,IF(AND($C4="Phuket",$A4="full-day"),Accommodation!$C4,IF(AND($C4="Phuket",$A4="two-day"),Accommodation!$C$5,IF(AND($C4="Phuket",$A4="longer than two days"),Accommodation!$C$6,IF(AND($C4="Koh Samui",$A4="shorter than half-day"),Accommodation!$D$2,IF(AND($C4="Koh Samui",$A4="half-day"),Accommodation!$D$3,IF(AND($C4="Koh Samui",$A4="full-day"),Accommodation!$D$4,IF(AND($C4="Koh Samui",$A4="two-day"),Accommodation!$D$5,IF(AND($C4="Koh Samui",$A4="longer than two days"),Accommodation!$D$6,IF(AND($C4="Hua Hin",$A4="shorter than half-day"),Accommodation!$E$2,IF(AND($C4="Hua Hin",$A4="half-day"),Accommodation!$E$3,IF(AND($C4="Hua Hin",$A4="full-day"),Accommodation!$E$4,IF(AND($C4="Hua Hin",$A4="two-day"),Accommodation!$E$5,IF(AND($C4="Hua Hin",$A4="longer than two days"),Accommodation!$E$6,IF(AND($C4="Pattaya",$A4="shorter than half-day"),Accommodation!$F$2,IF(AND($C4="Pattaya",$A4="half-day"),Accommodation!$F$3,IF(AND($C4="Pattaya",$A4="full-day"),Accommodation!$F$4,IF(AND($C4="Pattaya",$A4="two-day"),Accommodation!$F$5,IF(AND($C4="Pattaya",$A4="longer than two days"),Accommodation!$F$6,IF(AND($C4="Chiang Mai",$A4="shorter than half-day"),Accommodation!$G$2,IF(AND($C4="Chiang Mai",$A4="half-day"),Accommodation!$G$3,IF(AND($C4="Chiang Mai",$A4="full-day"),Accommodation!$G$4,IF(AND($C4="Chiang Mai",$A4="two-day"),Accommodation!$G$5,IF(AND($C4="Chiang Mai",$A4="longer than two days"),Accommodation!$G$6, IF(AND($C4="Singapore",$A4="shorter than half-day"),Accommodation!$H$2,IF(AND($C4="Singapore",$A4="half-day"),Accommodation!$H$3,IF(AND($C4="Singapore",$A4="full-day"),Accommodation!$H$4,IF(AND($C4="Singapore",$A4="two-day"),Accommodation!$H$5,IF(AND($C4="Singapore",$A4="longer than two days"),Accommodation!$H$6, IF(AND($C4="Hong Kong",$A4="shorter than half-day"),Accommodation!$I$2,IF(AND($C4="Hong Kong",$A4="half-day"),Accommodation!$I$3,IF(AND($C4="Hong Kong",$A4="full-day"),Accommodation!$I$4,IF(AND($C4="Hong Kong",$A4="two-day"),Accommodation!$I$5,IF(AND($C4="Hong Kong",$A4="longer than two days"),Accommodation!$I$6,IF(AND($C4="Kuala Lumpur",$A4="shorter than half-day"),Accommodation!$J$2,IF(AND($C4="Kuala Lumpur",$A4="half-day"),Accommodation!$J$3,IF(AND($C4="Kuala Lumpur",$A4="full-day"),Accommodation!$J$4,IF(AND($C4="Kuala Lumpur",$A4="two-day"),Accommodation!$J$5,IF(AND($C4="Kuala Lumpur",$A4="longer than two days"),Accommodation!$J$6,IF(AND($C4="Jakarta",$A4="shorter than half-day"),Accommodation!$K$2,IF(AND($C4="Jakarta",$A4="half-day"),Accommodation!$K$3,IF(AND($C4="Jakarta",$A4="full-day"),Accommodation!$K$4,IF(AND($C4="Jakarta",$A4="two-day"),Accommodation!$K$5,IF(AND($C4="Jakarta",$A4="longer than two days"),Accommodation!$K$6,IF(AND($C4="Siem Reap",$A4="shorter than half-day"),Accommodation!$L$2,IF(AND($C4="Siem Reap",$A4="half-day"),Accommodation!$L$3,IF(AND($C4="Siem Reap",$A4="full-day"),Accommodation!$L$4,IF(AND($C4="Siem Reap",$A4="two-day"),Accommodation!$L$5,IF(AND($C4="Siem Reap",$A4="longer than two days"),Accommodation!$L$6,IF(AND($C4="Yangon",$A4="shorter than half-day"),Accommodation!$M$2,IF(AND($C4="Yangon",$A4="half-day"),Accommodation!$M$3, IF(AND($C4="Yangon",$A4="full-day"),Accommodation!$M$4,IF(AND($C4="Yangon",$A4="two-day"),Accommodation!$M$5,IF(AND($C4="Yangon",$A4="longer than two days"),Accommodation!$M$6 ,IF(AND($C4="Colombo",$A4="shorter than half-day"),'ACCOMMODATION'!$N$2,IF(AND($C4="Colombo",$A4="half-day"),'ACCOMMODATION'!$N$3,IF(AND($C4="Colombo",$A4="full-day"),'ACCOMMODATION'!$N$4,IF(AND($C4="Colombo",$A4="two-day"),'ACCOMMODATION'!$N$5,IF(AND($C4="Colombo",$A4="longer than two days"),'ACCOMMODATION'!$N$6 ))))))))))))))))))))))))))))))))))))))))))))))))))))))) ))))) )))))

 

[Macropod]

You really shouldn't be trying to use IF tests for that. Apart from anything else, they're a maintenance nightmare. And, as you have discovered, you can only nest them so far...

What you should do is create a lookup table, which would then allow you to use one of Excel's LOOKUP functions or an INDEX/MATCH function combination.

 

[Nishant94]

Looks like you already have a table in "Accommodation" sheet.

Make your table like this in "Accommodation" Sheet:

Note: The numbers generated are random numbers as I don't know the result of matching criteria.

Excel 2016
	A	B	C	D	E	F
1		shorter then half-day	half-day	full-day	two-day	longer then two day
2	Bangkok	292	128	157	485	214
3	phuket	244	263	165	386	258
4	Koh Samui	320	445	191	431	407
5	Hua Hin	228	491	321	141	424
6	Pattaya	177	222	204	322	203
7	Chiang Mai	193	186	125	169	139
8	Singapore	304	398	261	269	221
9	Hong Kong	493	135	427	241	330
10	Kuala Lumpur	274	305	427	215	419
11	Jakarta	441	139	128	419	499
12	Siem Reap	242	321	400	374	273
13	Yangon	243	399	319	138	493
14	Colombo	363	487	401	448	141
Accommodation

And your Result sheet like this:

Excel 2016
	A	B	C	D	E	F
1		shorter then half-day	half-day	full-day	two-day	longer then two day
2	Bangkok	292	128	157	485	214
3	phuket	244	263	165	386	258
4	Koh Samui	320	445	191	431	407
5	Hua Hin	228	491	321	141	424
6	Pattaya	177	222	204	322	203
7	Chiang Mai	193	186	125	169	139
8	Singapore	304	398	261	269	221
9	Hong Kong	493	135	427	241	330
10	Kuala Lumpur	274	305	427	215	419
11	Jakarta	441	139	128	419	499
12	Siem Reap	242	321	400	374	273
13	Yangon	243	399	319	138	493
14	Colombo	363	487	401	448	141
Accommodation

[TABLE="width: 85%"]
<tbody></tbody>[/TABLE]

 

[Beunite]

Hi guys, thank you so much for your reply. I have studied the lookup, vlookup and index/match functions but wasn't able to make it work.

Here is a more detailed description of the problem:

On one work sheet (Calculation) I have Destination on column E, and category in Column F. It looks like this:

Calculation
E F
1 Bangkok Half-day
2 Phuket Full-day
3 Koh Samui Two-day

And in another spreadsheet (Accommodation) I have....

Accommodation
A B C D E
Duration Bangkok Phuket Koh Samui
1 Half-day 3 43 43
2 Full-day 5 61 72
3 Two-day 8 75 80

The end result should be in spreadsheet "Calculation" in column DW

Calculation
DW
1 3
2 61
3 80

Can somebody please help me with a substitute formula for ifand? An easy low maintenance solution?

 

[Beunite]

For better lay out:

[TABLE="width: 520"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD]Calculation[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]E [/TD]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] Bangkok [/TD]
[TD]Half-day[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] Phuket [/TD]
[TD]Full-day[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] Koh Samui [/TD]
[TD]Two-day[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Accommodation[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD] B[/TD]
[TD] C[/TD]
[TD] D[/TD]
[TD] E [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Duration[/TD]
[TD] Bangkok [/TD]
[TD]Phuket [/TD]
[TD]Koh Samui[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Half-day [/TD]
[TD]3[/TD]
[TD]43[/TD]
[TD]43[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] Full-day [/TD]
[TD]5[/TD]
[TD]61[/TD]
[TD]72[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Two-day[/TD]
[TD]8[/TD]
[TD]75[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]Calculation (formula retreives answer in column DW)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]DW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]61[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Calculation (end result)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]E [/TD]
[TD]F[/TD]
[TD]DW[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] Bangkok [/TD]
[TD]Half-day[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] Phuket [/TD]
[TD]Full-day[/TD]
[TD]61[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] Koh Samui [/TD]
[TD]Two-day[/TD]
[TD]80[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Nishant94]

Accommodation Table:

Excel 2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	S.No	Country Duration	Bangkok	phuket	Koh Samui	Hua Hin	Pattaya	Chiang Mai	Singapore	Hong Kong	Kuala Lumpur	Jakarta	Siem Reap	Yangon	Colombo
2	1	shorter then half-day	125	440	218	407	103	444	49	87	429	374	380	482	492
3	2	half-day	313	383	481	135	148	64	217	5	182	95	55	334	16
4	3	full-day	236	85	480	64	163	57	240	209	298	84	471	74	411
5	4	two-day	283	138	317	306	345	191	424	81	44	431	109	292	379
6	5	longer then two day	267	421	350	101	272	204	189	213	378	362	451	139	187
Accommodation

Result Sheet:

Excel 2016
	E	F	DW
1	Country	Day	Result
2	Bangkok	shorter then half-day	125
3
Calculation

Cell Formulas
Range	Formula
DW2	=VLOOKUP(F2,Accommodation!$B$2:$O$6,MATCH(E2,Accommodation!$B$1:$O$1,0),0)

 

[Beunite]

Hi Nishant 94,

This was fantastic. Such a wonderful formula. It works like a beauty. You have saved me tons of time.

Big thank you and my appreciation for your help

Kind regards
Beunite

p.s great forum and I will come back here next time.