Combine multiple IF statements in Excel 3

[Celeste025]

I am trying to write a formula for Excel 97 that will combine multiple IF statements in one line.. what I have so far is:

=IF(B1=1,E1)

I need it to continue to say something like:

ELSEIF(B1=2,E2), ELSEIF(B1=3,E3), ELSEIF(B1=4,E4), ELSEIF(B1=5,E5) etc etc... but elseif isnt working. Ive done it before but cant remember how now...

Thanks!

 

[Wray69]

=if(B1=2,E2,if(B1=3,E3,if(B1=4,E4,if(B1=5,E5,if(B1=6,E6)))))
KEEP IN MIND THOUGH YOU CAN ONLY HAVE 7 NESTED IF'S

Regards,

Wray

 

[Zathras]

Of course, if what you are doing is really that relationship based on the contents of B1 (1=E1, 2=E2, 3=E3, etc.) then you could use the INDIRECT function like this:
[blue]

   =INDIRECT("E"&B1)

[/color]

 

[Celeste025]

Wray,

Thank you very much, the nested If statement worked beautifully!

Celeste

 

[krinid]

Wray,
Is 7 nested IF's a hard limit, or is controllable by some environment setting (somewhat like recently used files list)?

 

[Wray69]

Krinid,

As far as I know 7 nested IF's is a hard limit. Really ifyou have more than that you probally would be better off makin a table of your data and using VLOOKUP

Regards,

Wray

 

[waynerenaud]

I thought Zathras idea was spot on. It helped me learn so Zathras you get a star

 

[krinid]

Wray69,
I agree, more than 7 IF's and there's probably a better way. Most of the time I've seen people nest >3 IF statements, it's b/c that's the only way they knew how to do it, despite better/shorter methods being available.
Didn't know there was a limit, though! I probably won't ever nest that many myself, but it's good bit of knowledge to know in case I come across it. Thanks.

 

[xlbo]

You can get around the NESTED limit by not nesting...
=IF(A1=1,2,"&quot&if(A1=2,3,"&quot&if(A1=3,4,"&quotetc etc ad nauseum

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes
Get the best answers to your questions - faq222-2244