Nested IF statement

[Rowdy]

Can anyone tell me if there is a arguement limit when
using Nested IF statements in excel. The statement works
with 8 arguements

=IF(D2="401K","01",IF(D2="CRUNIO","02",IF
(D2="REGEARN","03",IF(D2="OTEARN","04",IF
(D2="HOLEARN","05",IF(D2="VACEARN","06",IF
(D2="SICKEARN","07",IF(D2="OIPEARN","08"," "))))))))

Once I add the 9th argument I get an error message "The
formula you typed has an error".

=IF(D2="401K","01",IF(D2="CRUNIO","02",IF
(D2="REGEARN","03",IF(D2="OTEARN","04",IF
(D2="HOLEARN","05",IF(D2="VACEARN","06",IF
(D2="SICKEARN","07",IF(D2="OIPEARN","08",IF
(D2="FUNERALEARN","09"," ")))))))))

I actually have 20 arguement but can't get past 8.

I would appreciate anyones help.

Thanks

 

[Paul B]

Rowdy, from excel help, Maximum number of nested levels of functions 7

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **

 

[John Wilson]

Rowdy,

You'll need to use VLookup to accomplish what you want.
(Lokk in Excel help for more info on the VLookup)

In a range somewhere (for the sake of argument AA1 to AB20)
Col AA wil have CRUNIO, OTEARN, VACTEARN, etc.
Col AB will have your "02", "04", "06", etc.

Now the formula to get what you want would be:

=VLOOKUP(D2,AA1:AB20,2,FALSE)

John

 

[Bob Phillips]

Rowdy,

You have hit the wall? Excel only allows one outer, and 7 nested Ifs, giving
a total of 8.

Also, even 8 is unwieldy, 9 would be worse. You can make it much better by
building a 2-D table of values and associated values, and doing a VLOOKUP,
such as

=VLOOKUP(D2,H1:I10,2,FALSE)

The false is important in case the table is not sorted alphabetically it
will do a best match.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)