How can use IF function more than 7 times in a cell

[Guest]

Excel limits the function IF at 7 times, how can I set IF more than 7 times
in a cell

 

[Frank Kabel]

Hi
you can't. But in most cases there're better ways to achieve this. e.g.
use a lookup table and vLOOKUP.
You may post some more details what you're trrying to evaluate (post
your conditions and the desired output or the formula you have tried
already)

 

[Aladin Akyurek]

Try to re-express the task in terms of a lookup formula with LOOKUP() o
VLOOKUP, etc.

 

[Arvi Laanemets]

Hi

Instead nested IF's you can use:

1. When the result will be numeric
=LogicalCondition1*Result1+LogicalCondition2*Result2+..
2. When the result will be text
=IF(LogicalCondition1,Result1,"") & IF(LogicalCondition2,Result2,"") & ..
3. VLOOKUP function like adviced by Frank and Aladin. You can use a lookup
table (usually it will be on separate sheet), or you use an array as
function parameter instead.
4. INDEX function. It'll be especially useful when for some reason you
prefer the lookup table in matrix form.
5. A combination of MATCH and CHOOSE functions (it's limited with 24
different choices), like
=CHOOSE(MATCH(ValueToSearch,{Value1,Value2,...},0),Choice1,Choice2,...)
when exact match is searched for, or
=CHOOSE(MATCH(ValueToSearch,{Value1,Value2,...},1),Choice1,Choice2,...)
when the search value must be >=Value(n) and <Value(n+1)
Choices can be as numeric so texts.
6. A reduced formula like in previous paragraph - when you instead using
MATCH use some numeric expression which returns integer values 1 or 2 or
..... To be exact, these values mustn't be integers - the decimal part is
simply ignored.
=CHOOSE(NumericExpression, Choice1, Choice2, ...)
Again, you are limited to 24 differnt choices there.

 

[macropod]

The '7' limit only applies to nested IFs. If you don't nest you can use
more, but there are almost always better ways to approach such a problem,
some of which other have suggested.

Cheers