nested if formula

[Cossie]

Hello everyone I need some assistance with a nested if statement.

My formula looks like this but it has an error.

=IF(A2="n",IF(B2="under 12 months",0,IF(A2="Y",IF(B2="under 12 months",0,IF(A2="n",IF(B2="12-24 months",750,IF(A2="y",IF(B2="12 -24 months",1100,IF(A2="N",IF(B2="24 -36 months",1500,IF(A2="y",IF(B2="24 - 36 months",1500,IF(A2="n",IF(B2="36 months plus",2000,IF(A2="y",B2="36 months plus",2000))))))))))))))).

Any ideas for a better formula

 

[erik.van.geit]

Hi, Cossie,

you cannot nest more then 7 IFs
you can put in a little table what the options + outcome are: so someone can see what you want to do (I can imagine)
seems like you could use some Vlookup-formula
so take a look in the helpfiles for Vlookup (perhaps Index & Match)

kind regards,
Erik

 

[barry houdini]

Perhaps use a table like this

Book1
	A	B	C	D	E	F	G	H
1						Y	N
2	N	12-24 months	750		Under 12 months	0	0
3					12-24 months	1100	750
4					24-36 months	1500	1500
5					36 months plus	2000	2000
6
Sheet4

Formula in C2

=INDEX(F$2:G$5,MATCH(A2,F$1:G$1,0),MATCH(B2,E$2:E$5,0))

or if you want to do it without a table then try this formula

=INDEX({0,0;1100,750;1500,1500;2000,2000},MATCH(A2,{"Y","N"},0),MATCH(B2,{"Under 12 months","12-24 months","24-36 months","36 months plus"},0))