If Statement

E

ernestgoh

hi, just trying to find out if there is a maximum number of IFs you ca
use in a statement in one cell. I can't use more than 9 IFs in 1 cel
else will get an error message. Is there any way to overcome this?
could use vlookup method but they mean changing my current format
thanks!

Example:
=IF(Plan_ANB=1,BJ5,IF(Plan_ANB=2,BJ21,IF(Plan_ANB=3,BJ37,IF(Plan_ANB=4,BJ53,IF(Plan_ANB=5,BJ69,IF(Plan_ANB=6,BJ85,IF(Plan_ANB=7,BJ101,IF(Plan_ANB=8,BJ117,BJ149)))))))
 
M

Mangesh Yadav

VLOOKUP is the best option, and IF has a limit so use VLOOKUP.

But if your example is the actual fromula you are using, then try the
following formula:

=INDIRECT("BJ"&5+(Plan_ANB-1)*16)

no need of the IF, just try exactly what I have given on top. It will give
the same result as your example.


Mangesh
 
P

Paul Sheppard

ernestgoh said:
hi, just trying to find out if there is a maximum number of IFs you can
use in a statement in one cell. I can't use more than 9 IFs in 1 cell
else will get an error message. Is there any way to overcome this? I
could use vlookup method but they mean changing my current format.
thanks!

Example:
=IF(Plan_ANB=1,BJ5,IF(Plan_ANB=2,BJ21,IF(Plan_ANB=3,BJ37,IF(Plan_ANB=4,BJ53,IF(Plan_ANB=5,BJ69,IF(Plan_ANB=6,BJ85,IF(Plan_ANB=7,BJ101,IF(Plan_ANB=8,BJ117,BJ149))))))))
Click to expand...

Hi ernestgoh

IF() statements can only be nested 7 deep, which means the max in a
statement is 8 IF's.

For a workaround, see

http://cpearson.com/excel/nested.htm
 
A

aaron.kempf

yeah.. at this point; it is better do to a JOIN in a database instead
of having 20 nested if-then statements
 
T

Tom Ogilvy

No need to do either:

=INDIRECT("BJ"&5+(Plan_ANB-1)*16) as suggested by Mangesh


certainly isn't complex.
 
Top