Problem with multiple if

Hi

I have created the following if function which works well.

=(IF(C14="SALARIED",$D$2,IF(C14="JH",$D$3,IF(C14="KH",$D$4,IF(C14="CS 3",$D$5,IF(C14="SH AM",$D$6,IF(C14="AJ MON",$D$7,IF(C14="AJ OTHE",$D$8,IF(C14="AJ WED",$D$9,"")))))))))

I now want to add another condition at the end as follows but I get the error message shown underneath the formula: -

=(IF(C14="SALARIED",$D$2,IF(C14="JH",$D$3,IF(C14="KH",$D$4,IF(C14="CS 3",$D$5,IF(C14="SH AM",$D$6,IF(C14="AJ MON",$D$7,IF(C14="AJ OTHE",$D$8,IF(C14="AJ WED",$D$9,if(C14="HP 2",$D$10,""))))))))

"The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format"

Why do I get this message? I'd appreciate anybodies help on this.

Many thanks

There's a limit to how many nested loops you can have, which is why Excel refuses to accept another level.

Instead you could try something like:

=OFFSET($D$1,MATCH(C14,{"SALARIED","JH","KH","CS 3","SH AM","AJ MON","AJ OTHE","AJ WED","HP 2"},0),0)

You can only nest 7 IFS inside each other.

Well, that's pretty circular compared to what you already know, so I'll explain a little deeper.

Putting one IF inside another: IF(check, do_something, IF(check, do_something, do_something_else) is called nesting. So, that's got two IFs, one nested inside another. The function you already have, has seven IFs nested like Matroyshka dolls.

When you try to wrap another one into it, that's the eighth nested IF; that's too many, excel simply can't handle that much.

Anyway, with the setup you've got, I'd recommend you create a table and pump through that with a LOOKUP, instead of doing IFs.

Like this:
=VLOOKUP(c14, that_table, 0)

"that_table" can either be entered into the function {as an array} or in a section of helper cells.

Many thanks for all your replies - I do have 2010 version however I was saving this as 2003 which was causing the problem. As soon as I re-saved this as 2010, closed and re-opened it it worked! :-)

Cheers for that.