Struggling with IF(AND formula (Please Help)

[ericcarl]

Hello Forum,
I'm struggling with a nested formula that uses several IF statements and two IF(AND) statements. The formula is shown below. The error message I receive is "The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format". The two IF(AND statements at the end worked fine with just the two of them. I thought Excel could handle many more levels of nesting than just this? At the end of the formula, cells K12 and K13 refer to locations as in the IF statements that precede them.


Can anyone please tell me what I'm doing wrong here? I'm sure it's something simple that I'm overlooking - I'm just not very good at multiple nesting in a formula. Your help is greatly appreciated!


=IF(H2="Gala","Oregon",IF(H2="Jazz","Maine",IF(H2="Golden Delicious","California",IF(H2="Granny Smith","Washington",IF(H2="Braeburn","Florida",IF(H2="Lady","Idaho",IF(H2="Gravenstein","New York",IF(AND(H2="Honeycrisp",AA2="ILA"),K12,IF(AND(H2="Honeycrisp",AA2="ILD"),K13,"Wrong Value")))))))))


Thank you in advance,
greykitten

 

[sheetspread]

Are you using an Excel earlier than 2007? Then the limit is 6 or 7. This worked fine when I tested in version 2010. But you really should consider a lookup table in the formula or somewhere on the worksheet instead.

 

[ericcarl]

Sorry, I should have specified the version of Excel. Yes, it is on xls (97-2003). Hypothetically, the formula could run in an xlsx version of the file and then the values could be cut and pasted as only values and then saved as xls. Do you think VLOOKUP would be more effective? How would that look in practice where the final two checks have to meet two conditions? Thanks so much for your help!

 

[sheetspread]

How about:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	y	Gala	Oregon
2		Jazz	Maine					Honeycrisp																			ILA
3		Golden Delicious	California
4		Granny Smith	Washington
5		Braeburn	Florida
6		Lady	Idaho
7		Gravenstein	New York
8
9
10
11
12											x
13											y
Sheet5

Cell Formulas
Range	Formula
A1	=IF(AND(H2="Honeycrisp",AA2="ILD"),K12,IF(AND(H2="Honeycrisp",AA2="ILA"),K13,IF(ISERROR(VLOOKUP(H2,$B$1:$C$7,2,0))=FALSE,VLOOKUP(H2,$B$1:$C$7,2,0),"Wrong Value")))

 

[ron2k]

Does this help? I cut couple IFs:

=IF(AND(H2="Honeycrisp",AA2="ILA"),K12,IF(AND(H2="Honeycrisp",AA2="ILD"),K13,IF(H2<>"Honeycrisp",CHOOSE(MATCH(H2,{"Gala","Jazz","Golden  Delicious","Granny  Smith","Braeburn","Lady","Gravenstein"},0),"Oregon","Maine","California","Washington","Florida","Idaho","New  Yor"),"Wrong Value")))

 

[ericcarl]

Sheetspread,
Thank you SO much for your help!! I really appreciate it

~ ericcarl