Max nested If functions

[jmersing]

This is my formula :

=IF($D15="",(VLOOKUP($E15,Shifts!$A$2:$E$109,4,0)),IF($D15="OT",$DX15, IF($D15="F",$DX15,IF($D15="V","Vacation",IF($D15="P","Personal",IF($D15="M","MLOA",IF(VLOOKUP($E15,Shifts!$A$2:$E$109,4,0)="","Off",(VLOOKUP($E15,Shifts!$A$2:$E$109,4,0)))))))))

I'm out of IFs!

I have two more conditions to add.

Thanks

[vconfused]

Can you use two cells somewhere else and have them each have half of the ifs? You could have them both return "FALSE" or something and then in your final destination cell just have an IF() to return the value from either of those two?

[shades]

Chip Pearson shows how to extend the nested IFs.

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

[texasalynn]

Originally Posted by jmersing

This is my formula :

=IF($D15="",(VLOOKUP($E15,Shifts!$A$2:$E$109,4,0)),IF($D15="OT",$DX15, IF($D15="F",$DX15,IF($D15="V","Vacation",IF($D15="P","Personal",IF($D15="M","MLOA",IF(VLOOKUP($E15,Shifts!$A$2:$E$109,4,0)="","Off",(VLOOKUP($E15,Shifts!$A$2:$E$109,4,0)))))))))

I'm out of IFs!

I have two more conditions to add.

Thanks

Your could combine one set
=IF($D15="",(VLOOKUP($E15,Shifts!$A$2:$E$109,4,0)),IF(or($D15="OT",$D15="F"),$DX15,IF($D15="V","Vacation",IF($D15="P","Personal",IF($D15="M","MLOA",IF(VLOOKUP($E15,Shifts!$A$2:$E$109,4,0)="","Off",(VLOOKUP($E15,Shifts!$A$2:$E$109,4,0))))))))

HTH
texasalynn

[jmersing]

Looks like two ggod ways to solve the problem. One question for Shades. The link you gave me instructs you to create a named formula, a user defined named for a formula?

I did not realize you can do that. How do you name a formula?

Thanks

[saurabh726]

Well check it this out...

http://www.cpearson.com/excel/named.htm

Is this what u have been hunting for..??

Saurabh...

[jmersing]

Yes saurabh726
Shades provided the link earlier in the post, but thanks for your response!

[fairwinds]

Hi,

Try:

=IF(D15="",IF(VLOOKUP($E15,Shifts!$A$2:$E$109,4,0)="","Off",VLOOKUP($E15,Shifts!$A$2:$E$109,4,0)),CHOOSE(MATCH(D15,{"OT","F","V","P","M"},0),$DX15,$DX15,"Vacation","Personal","MLOA"))

[Aladin Akyurek]

Originally Posted by fairwinds

...

=IF(D15="",IF(VLOOKUP($E15,Shifts!$A$2:$E$109,4,0)="","Off",VLOOKUP($E15,Shifts!$A$2:$E$109,4,0)),CHOOSE(MATCH(D15,{"OT","F","V","P","M"},0),$DX15,$DX15,"Vacation","Personal","MLOA"))

The trouble is that, if D15 is e.g., OT, we won't get the value of $DX15.

The question is whether the outcomes associated with D15 are mutually exclusive. If so, a multi-cell setup would be better a chained set of IFs.

[fairwinds]

Originally Posted by Aladin Akyurek

The trouble is that, if D15 is e.g., OT, we won't get the value of $DX15.

Yes you will. Try it.