If formula nesting

C

ccraig61

Does any one know if there is a limited number of arguments one can nest
in an IF formula? I have tried entering 14 conditions and it will not
accept after the 8th one. It tells me I have no false value. Or is
there something wrong with my formula?

=IF(H45=43.9,
"Needles",IF(H45=21.95,"Needles",IF(H45=62.5,"HP",IF(H45=65,"HP",IF(H45=67.5,"HP",IF(H45=70,"HP",IF(H45=130,"HP",IF(H45=135,"HP",if(h45=140,"HP",if(h34=995,"CD",if(h34=1295,"CD",if(h34=1695,"CD",if(H34=1990,"CD",if(h34=2190,"CD","
"))))))))))))))
 
H

hansyt

There is a maximum of 7 nested IFs. But some of your conditions can be
combined such as

=if(or(H45=43,9;h45=21.95));"Needles";if(.....

But I would recommend to use Vlookup such as

search via vlookup standard table

45.6 Needels 15.7 Needles
63.1 HP 16.3 Canon
15.7 Needles 24.8 HP
24.8 HP 45.6 Needels
16.3 Canon 63.1 HP

Formula in column B:
=IF(ISNA(VLOOKUP(A2;$D$2:$E$6;2;FALSE));"not in
list";VLOOKUP(A2;$D$2:$E$6;2;FALSE))

Replace the semicolons with commas if required.

Hans
 
R

Rajah

Hi, Craig,
You're right: the IF statement is limited in how deep it can go. The
online Excel help says you can only go 7 deep. The error message is not
very helpful.

You might consider using a CHOOSE statement for H45 and another one for
H34.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top