if statement problem ? length

[andrewm]

I am using a long if statement like the one below, whenever I add another cell such as j9 I get an error, what am I doing wrong or is there a limit to the if statement. I am using excel 97. thanks

=IF(J1="andrew","a",IF(J2="andrew","b",IF(J3="andrew","c",IF(J4="andrew","d",IF(J5="andrew","e",IF(J6="andrew","f",IF(J7="andrew'","g",IF(J8="andrew","h"))))))))


andrewm

[luke_w]

any solution to your problem? I have just encountered the same problem.

[Domenic]

Yes, there's a limit and you've reached it. Try the following instead...

=INDEX({"a","b","c","d","e","f","g","h"},MATCH("andrew",J1:J8,0))

OR

=INDEX(A1:A8,MATCH("andrew",J1:J8,0))

...where A1:A8 contains your values, such as "a", "b", "c", etc.

Hope this helps!

[andrewm]

I think it is go to do with excel 97, has this issue been rectified by newer versions or am i wrong

andrewm

[Domenic]

I believe that you can only nest up to 7 IF statements. Although, with my Mac version of Excel, I'm able to nest up to 8. Did you find my solution helpful?

[MrShorty]

I think it is go to do with excel 97, has this issue been rectified by newer versions or am i wrong

andrewm

Excel 2002 claims only 7 nested IF functions. I haven't heard that 2003 allows for more nesting.
It looks like your best approach is going to be a lookup table/array like domenic is proposing.