IF function has more than 64 levels of nesting

welcome to the forum

Looking at that monster....ummm noway!!! Im not even goin to try and decypher that. I think you need to look into creating a table of values and using index/match or vlookup.

Walk me (slowly) through what you are trying to do?

(I have to say, that is probably the biggest, messyest formula I have seen - no disprespect intended, I can appreciate the effort that went into giving birth to that)

Seems to me you could tabulate all of the possibilities ...

	B	C	D
10	Single	Single	>=1/1/2013
11	Single	Single	>=1/1/2014
12	Single	Married/Remarried	>=1/1/2013
13	Single	Married/Remarried	>=1/1/2014
14	Single	Separated	>=1/1/2013
15	Single	Separated	>=1/1/2014
16	Single	Divorced	>=1/1/2013
17	Single	Divorced	>=1/1/2014
18	Single	Widowed	>=1/1/2013
19	Single	Widowed	>=1/1/2014
20	Married-Joint	Single	>=1/1/2013
21	Married-Joint	Single	>=1/1/2014
22	Married-Joint	Married/Remarried	>=1/1/2013
23	Married-Joint	Married/Remarried	>=1/1/2014
24	Married-Joint	Separated	>=1/1/2013

... add a column for resolution, and use a simple formula to return the value where all the criteria are met.

Thanks for the assist shg. Not sure how you weedled that tabke from that formula, but great job

You call yourself a beginner, but I would completely disagree. I doubt any beginner would be able to string together a beast like that - that showed a level of competance

Based on shg's table I modified it a bit....

	A	B	C	D
1	Cat1	Cat2	1/1/2013	1/1/2014
2	Single	Single	aa	aa11
3	Single	Married/Remarried	bb	bb11
4	Single	Separated	cc	cc11
5	Single	Divorced	dd	dd11
6	Single	Widowed	ee	ee11
7	Married-Joint	Single	ff	ff11
8	Married-Joint	Married/Remarried	gg	gg11
9	Married-Joint	Separated	hh	hh11
10
11	Cat1	Single
12	Cat2	Divorced
13	date	6/6/2014
14		dd11

B14=INDEX($C$2:$D$9,MATCH(B11&"-"&B12,INDEX($A$2:$A$9&"-"&$B$2:$B$9,0),0),MATCH(B13,$C$1:$D$1,1))

Glad you got it resolved, and thanks for the feedback (both)

red123 welcome to the forum

wow, that is some monster!!
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

No problem, I will keep an eye open for your new thread on this

Thanks again for all your help! I used what you showed me in conjunction with a few youtube videos and voila!!! My first array! Thanks again for all your help and encouragement!!!!

I found your problem in google searching for the same problem of excess levels to 64. So would have you newly found array example handy to send me or show?

Below is what I have: If the object meets the range of the formula I get a boolean answer

=
IF(AND($AA$2<=0.78,$U$2>=1,$U$1=9,$K$4="WET",$B$5>=13,$AA$2<0.7,$Y7="YES6"),"YES",
IF(AND($AA$2<=0.78,$U$2>=1,$U$1=9,$K$4="DRY",$B$5>=13,$AA$2<0.7,$Y7="YES6"),"YES",
IF(AND($AA$2<=0.78,$U$2>=1,$U$1=7,$B$5<13,$AA$4<$AA$1,$AA7>=($AA$1*0.13),$X$1<=4,$Y7="YES6"),"YES",
IF(AND($AA$2<=0.78,$U$2>=1,$B$5<13,$J$2<=0.986,$X$1<=2,$Y7="YES6"),"YES",
IF(AND($J$2<=0.986,($X$2+X$3)=4,$U$2>=1,$AA$2<=0.89,$AA7>=($AA$1*0.13),$X$1<=4,$Y7="YES6"),"YES",
IF(AND($J$2<=0.986,$U$2>=1,$AA$2<=0.78,$AA$4<$AA$1,$B$5<13,$X$1<=4,$Y7="YES6"),"YES",
IF(AND($J$2<=0.986,$AA$2<=0.78,$U$2>=1,$AA7>=($AA$1*0.001),$X$1<=1,$Y7="YES6"),"YES",
IF(AND($U$2>=1,$AA$4<$AA$1,$J$2<=0.986,$AA7>=($AA$1*0.51),$B$5<13,$Y7="YES6"),"YES",

IF(AND($AA$2<=0.78,$U$2>=1,$U$1=9,$K$4="WET",$B$5>=13,$AA$2<0.7,$Y7="YES6A"),"YES",
IF(AND($AA$2<=0.78,$U$2>=1,$U$1=9,$K$4="DRY",$B$5>=13,$AA$2<0.7,$Y7="YES6A"),"YES",
IF(AND($AA$2<=0.78,$U$2>=1,$U$1=7,$B$5<13,$AA7>=($AA$1*0.13),$X$1<=4,$Y7="YES6A"),"YES",
IF(AND($AA$2<=0.78,$U$2>=1,$B$5<13,$J$2<=0.986,$X$1<=2,$Y7="YES6A"),"YES",
IF(AND(($X$2+X$3)=4,$U$2>=1,$AA$2<=0.78,$AA7>=($AA$1*0.13),$X$1<=4,$Y7="YES6A"),"YES",
IF(AND($J$2<=0.986,$U$2>=1,$AA$4>$AA$1,$AA$2<=0.78,$B$5<13,$X$1<=4,$Y7="YES6A"),"YES",
IF(AND($AA$2<=0.78,$U$2>=1,$X$1<=1,$Y7="YES6A"),"YES",

IF(AND($AA$2<=0.78,$U$2>=1,$U$1=9,$K$4="WET",$B$5>=13,$AA$2<0.7,$Y7="YES9"),"YES",
IF(AND($AA$2<=0.78,$U$2>=1,$U$1=9,$K$4="DRY",$B$5>=13,$AA$2<0.7,$Y7="YES9"),"YES",
IF(AND($AA$2<=0.78,$U$2>=1,$U$1=7,$B$5<13,$AA7>=($AA$1*0.13),$X$1<=4,$Y7="YES9"),"YES",
IF(AND($AA$2<=0.78,$U$2>=1,$B$5<13,$J$2<=0.986,$X$1<=2,$Y7="YES9"),"YES",
IF(AND(($X$2+X$3)=4,$U$2>=1,$AA$2<=0.78,$AA7>=($AA$1*0.13),$X$1<=4,$Y7="YES9"),"YES",
IF(AND($J$2<=0.986,$U$2>=1,$AA$4>$AA$1,$AA$2<=0.78,$B$5<13,$X$1<=4,$Y7="YES9"),"YES",
IF(AND($AA$2<=0.78,$U$2>=1,$X$1<=1,$Y7="YES9"),"YES",

IF(AND($U$2>=1,$X$2<=2,$AA$2>=0.986,$Y7="YES2"),"YES",
IF(AND($U$2>=1,$X$2<=2,$AA$2<=0.986,$AA7>=($AA$2*0.01),$Y7="YES2"),"YES",
IF(AND($U$2>=1,($X$2+X$3)=4,$AA7>=($AA$3*0.005),$Y7="YES2"),"YES",
IF(AND($U$2>=1,$U$1<>8,$U$1<>9,$B$5<13,$AA$2>=0.8,$AA7>=($AA$2*0.005),$X$2=4,$Y7="YES2"),"YES",
IF(AND($U$2>=1,$U$1<>8,$U$1<>9,$B$5>=13,$AA$2>=0.8,$AA7>=($AA$2*0.005),$X$2=4,$Y7="YES2"),"YES",
IF(AND($U$2>=1,$X$2=4,$J$2<=0.986,$AA7>=($AA$2*0.005),$Y7="YES2"),"YES",
IF(AND($U$2>=1,$U$1=7,$AA7>=(($AA$2+$AA$3)*0.13),$Y7="YES2"),"YES",

IF(AND($U$2>=1,$X$2<=2,$AA$2>=0.9,$Y7="YES4"),"YES",
IF(AND($U$2>=1,$X$2<=2,$AA$2<=0.9,$AA7>=($AA$2*0.2),$Y7="YES4"),"YES",
IF(AND($U$2>=1,($X$2+X$3)=4,$AA7>=($AA$3*0.005),$Y7="YES4"),"YES",
IF(AND($U$2>=1,$U$1<>8,$U$1<>9,$B$5<13,$AA$2>=0.8,$AA7>=($AA$3*0.005),$X$2=4,$Y7="YES4"),"YES",
IF(AND($U$2>=1,$U$1<>8,$U$1<>9,$B$5>=13,$AA$2>=0.8,$AA7>=($AA$3*0.005),$X$2=4,$Y7="YES4"),"YES",
IF(AND($U$2>=1,$X$2=4,$AA7>=($AA$2*0.2),$Y7="YES4"),"YES",
IF(AND($U$2>=1,$U$1=7,$AA7>=(($AA$2+$AA$3)*0.13),$Y7="YES4"),"YES",

IF(AND($U$2>=1,$X$2<=2,$AA$2>=0.9,$Y7="YES4A"),"YES",
IF(AND($U$2>=1,$X$2<=2,$AA$2<=0.9,$AA7>=($AA$2*0.2),$Y7="YES4A"),"YES",
IF(AND($U$2>=1,($X$2+X$3)=4,$AA7>=($AA$3*0.005),$Y7="YES4A"),"YES",
IF(AND($U$2>=1,$U$1<>8,$U$1<>9,$B$5<13,$AA$2>=0.8,$AA7>=($AA$3*0.005),$X$2=4,$Y7="YES4A"),"YES",
IF(AND($U$2>=1,$U$1<>8,$U$1<>9,$B$5>=13,$AA$2>=0.8,$AA7>=($AA$3*0.005),$X$2=4,$Y7="YES4A"),"YES",
IF(AND($U$2>=1,$X$2=4,$AA7>=($AA$2*0.2),$Y7="YES4A"),"YES",
IF(AND($U$2>=1,$U$1=7,$AA7>=(($AA$2+$AA$3)*0.13),$Y7="YES4A"),"YES",

IF(AND($U$2>=1,$J$2<=0.986,$AA$2<=0.89,$AA7>=($AA$3*0.01),($X$2+X$3)=4,$Y7="YES3"),"YES",
IF(AND($U$2>=1,$J$2>=0.986,$AA$2<=0.79,$X$3=1,$X$2=4,$U$2>1,$J7<=6,$Y7="YES3"),"YES",
IF(AND($U$2>=1,$J$2<=0.986,$AA$2<=0.89,$U$1<>7,$X$3=1,$X$2=1,$AA7>=($AA$3*0.001),$Y7="YES3"),"YES",
IF(AND($U$2>=1,$J$2<=0.986,$AA$2<=0.89,$X$3=1,$AA$3>=0.01,$U$1<>7,$AA7>=($AA$3*0.001),$B$5<13,$Y7="YES3"),"YES",
IF(AND($U$2>=1,$J$2>=0.986,$X$3=1,$AA$3>=0.01,$AA7>=0.005,$B$5>13,$Y7="YES3"),"YES",
IF(AND($U$2>=1,$J$2>=0.986,$AA$2>=0.8,$X$3=1,$U$2>1,$U$1<>7,$J7<=4,$B$5<13,$Y7="YES3"),"YES",
IF(AND($U$2>=1,$J$2>=0.986,$X$3=1,$U$1<>7,$J7<=4,$B$5<13,$Y7="YES3"),"YES",
IF(AND($U$2>=1,$J$2<=0.986,$X$3=1,$U$1<>7,$AA7>=($AA$3*0.01),$B$5<13,$Y7="YES3"),"YES",
IF(AND($U$2>=1,$J$2>=0.986,$AA$2>=0.79,$U$1=9,$Y7="YES3"),"YES",

IF(AND($AA$2<=0.79,$U$2>=1,$U$1=7,$B$5<13,$AA$2<=0.9,$J$2<=0.986,$AA7>=($AA$4*0.3),$X$1<=2,$AA$1<0.03,$AA$4>=0.001,$Y7="11"),"YES",
IF(AND($AA$2<=0.79,$U$2>=1,$B$5<13,$J$2<=0.986,$AA$2<=0.9,$AA$1<0.03,$AA$4>=0.001,$AA7>=($AA$4*0.3),$X$4<=1,$Y7="11"),"YES",
IF(AND(OR((($X$2+X$3)=3),$AA$2<=0.79),$U$2>=1,$J$2<=0.986,$AA$2<=0.9,$AA$2<=0.79,$AA$1<0.03,$AA$4>=0.001,$AA7>=($AA$4*0.3),$X$4<=2,$Y7="11"),"YES",
IF(AND($U$2>=1,$J$2<=0.986,$AA$2<=0.79,$AA7>=($AA$4*0.3),$X$4<=1,$Y7="11"),"YES",

IF(AND($AA$2<=0.79,$J$2<=0.986,$U$2>=1,$U$1=9,$K$4="WET",$B$5>=13,$AA$2<=0.9,$AA$3<=0.003,$AA$1<0.03,$AA$4>=0.001,$Y7="YES1"),"YES",
IF(AND($AA$2<=0.79,$J$2<=0.986,$U$2>=1,$U$1=9,$K$4="DRY",$B$5>=13,$AA$2<=0.9,$AA$1<0.03,$AA$4>=0.001,$Y7="YES1"),"YES",
IF(AND($AA$2<=0.79,$J$2<=0.986,$U$2>=1,$U$1=7,$B$5<13,$AA$4>$AA$1,$AA$2<=0.9,$AA7>=($AA$4*0.3),$X$1<=2,$AA$1<0.03,$AA$4>=0.001,$Y7="YES1"),"YES",
IF(AND($AA$2<=0.79,$J$2<=0.986,$U$2>=1,$AA$2<=0.9,$AA$1<0.03,$AA$4>=0.001,$AA$4>$AA$1,$AA7>=($AA$4*0.3),$X$4<=1,$Y7="YES1"),"YES",
IF(AND(OR((($X$2+X$3)=3),$AA$2<=0.79),$J$2<=0.986,$U$2>=1,$AA$2<=0.9,$AA$4>$AA$1,$AA$2<=0.79,$AA$1<0.03,$AA$4>=0.001,$AA7>=($AA$4*0.3),$X$4<=2,$Y7="YES1"),"YES",
IF(AND($U$2>=1,$AA$2<=0.79,$B$5<13,$J$2<=0.986,$X$4=1,$AA7>=($AA$4*0.2),$Y7="YES1"),"YES",
IF(AND($U$2>=1,$AA$2<=0.79,$J$2<=0.986,$AA7>=($AA$4*0.01),$AA$4>$AA$1,$B$5<13,$Y7="YES1"),"YES",
IF(AND($U$2>=1,$AA$2<=0.9,$J$2<=0.986,$AA7>=($AA$4*0.51),$AA$4<$AA$1,$B$5<13,$Y7="YES1"),"YES",
" "))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

1ajdelacruz,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

1ajdelacruz,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

---

Rule #12: Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.

hello newbie here.
and how to use IF for PLUS? like this

Captures.PNG
=IF(C7="RNI UD",F7+30,IF(C7="HIDUP BARU",F7+30,IF(C7="ZUBIR UD",F7+30,IF(C7="TAMAN SARI BARU",F7+30,IF(C7="SUMBER REJEKI",F7+30,IF(C7="USAHA SAKTI",F7+30,IF(C7="MUTIARA JAYA",F7+30,IF(C7="HIDUP BARU 2",F7+30,IF(C7="PUTRO SAKTI",F7+30,IF(C7="ACR",F7+30,IF(C7="ILHAM",F7+30,IF(C7="SEJAHTERA GALERY",F7+30,IF(C7="NOUVAL",F7+30,IF(C7="AYAH GEUBRINA",F7+30,IF(C7="ARIZKY JAYA",F7+30,IF(C7="RAHMAD ILAHI",F7+30,IF(C7="SEJAHTERA FLORIS",F7+30,IF(C7="SUMBER AKA",F7+30,IF(C7="MBAK MOEL",F7+30,IF(C7="KIOS SYUKUR LAMTEMEN",F7+30,IF(C7="ZAKIR DARUSSALAM (WARKOP)",F7+30,IF(C7="MHD",F7+30,IF(C7="YSF TOKO",F7+30,IF(C7="SUMBER BARU",F7+30,IF(C7="ANEKA RAGAM",F7+30,IF(C7="ABADI JAYA BLANG BINTANG",F7+30,IF(C7="KUTA MULIA",F7+30,IF(C7="FAIZAH",F7+30,IF(C7="KS UMMI",F7+30,IF(C7="SEJAHTERA UTAMA",F7+30,IF(C7="LENTERA ABADI",F7+30,IF(C7="ARAFAH JAYA",F7+30,IF(C7="KAK ROSI",F7+30,IF(C7="TOKO ANOLA",F7+30,IF(C7="SUBUR JAYA",F7+30,IF(C7="ADEK ABANG",F7+30,IF(C7="ADI MARKET",F7+30,IF(C7="MINI MARKET ADEK ABANG",F7+30,IF(C7="BINA SEHAT APOTEK",F7+30,IF(C7="USAHA MANDIRI",F7+30,IF(C7="PRODATA FOTO COPY",F7+30,IF(C7="FIRMAN FOTO COPY",F7+30,IF(C7="YASIR MANDIRI",F7+30,IF(C7="TOSERBA AJI",F7+30,IF(C7="ILAH DAYA LAMNO",F7+30,IF(C7="BAHAGIA BARU",F7+30,IF(C7="RAMA JAYA",F7+30,IF(C7="GELORA",F7+30,IF(C7="ZEA BARONA",F7+30,IF(C7="SAMA-SAMA",F7+30,IF(C7="JASA BARU",F7+30,IF(C7="RAHMAT YH UD",F7+30,IF(C7="HARKAT JAYA",F7+30,IF(C7="WONG SOLO",F7+30,IF(C7="RAMLI",F7+30,IF(C7="RIZKY",F7+30,IF(C7="PULAU MAKMUR",F7+30,IF(C7="MULYA JAYA",F7+30,IF(C7="RUDA",F7+30,IF(C7="FANTASI COLECTIONS",F7+30,IF(C7="FZ LUENGBATA",F7+30,IF(C7="NAWI UD",F7+30,IF(C7="ALENTA (ALEX)",F7+30,IF(C7="JASA AYAH POMA",F7+30,IF(C7="RAHMAD YH UD",F7+30,IF(C7="PELANGI JAYA BARU",F7+30,IF(C7="ANANDA SWALAYAN",F7+30,IF(C7="YUSDA SAKTI",F7+30,IF(C7="MULYA JAYA",F7+30,IF(C7="RAJIN",F7+30,IF(C7="MUARA SABANG",F7+30,IF(C7="KUTA MULIA",F7+30,IF(C7="BERKAT KAWAN",F7+30,IF(C7="EVERLAND",F7+30,IF(C7="NURI FOTOCOPY",F7+30,IF(C7="ZUBIR",F7+30,IF(C7="SUBUR JAYA MOBIL",F7+30,IF(C7="PUSKOPAD",F7+30,IF(C7="EKA MULIA",F7+30,IF(C7="INDI FOTO COPY",F7+30,IF(C7="USAHA MAJU JAYA",F7+30,IF(C7="USAHA RAJIN",F7+30,IF(C7="FAJAR ASMARA",F7+30,IF(C7="INDOMER FOTOCOPY",F7+30,IF(C7="SUMBER HIDUP",F7+30,IF(C7="ANANDA SAKTI",F7+30,IF(C7="HAWAI UD",F7+30,IF(C7="RAHMAD",F7+30,IF(C7="FALAH JAYA",F7+30,IF(C7="AZRAH",F7+30,IF(C7="AMIR UD",F7+30,IF(C7="ARAFAH",F7+30,IF(C7="TIGA PUTRA SEJATI",F7+30,IF(C7="SINAR MAJU",F7+30,IF(C7="ABADI SEMBAKO",F7+30,IF(C7="HARI HARI",F7+30,IF(C7="KARONA UD",F7+30,IF(C7="SYUKUR NIKMAT",F7+300,F7+14