Excel Magic Trick #129: More than 7 Nested IF functions

[goodluckstatistics]

how do i do this in VBA

[ExcelIsFun]

@goodluckstatistics , I do not know, but the masters at:

mrexcel [dot] com

would know.

[abdulmaktedar]

Hey I have used the below nested If statement in Excel 2007 but I get an error stating more than allowed levels. Please help.

=IF($B$4=B21,"Actual",IF($B$4=­C21,"Actual",IF($B$4=D21,"Actu­al",IF($B$4=E21,"Actual",IF($B­$4=F21,"Actual",IF($B$4=G21,"A­ctual",IF($B$4=H21,"Actual",IF­($B$4=I21,"Actual",IF($B$4=J21­,"Actual",IF($B$4=K21,"Actual"­,IF($B$4=L21,"Actual",IF($B$4=­M21,"Actual","Flash")

[ExcelIsFun]

Maybe your file extension is ".xls"? If so change it to ".xlsx" or ".xlsm".

Also see this video, which may show a MUCH easier formula you could use:

Highline Excel Class 35: IF Function Vs. VLOOKUP Function

[ExcelIsFun]

Let me know if that does the trick.

[bingo3366]

Thank you lots and lots. God bless you!!!

[ExcelIsFun]

I am glad they helped!

[paramdeepwalia]

i have excel 2007 but when i type the formula with 13 if  i get a message that excel does not support more than 7 nested if statements please help

[ExcelIsFun]

It should still work. You might try making the file extension .xlsx or .xlsm.

[garethrmmiller]

I don't know what I am doing wrong but I have followed this video, obviously putting in my own "if" statements, and I have been given a "FALSE0" value. Any suggestions?

[ExcelIsFun]

If it gives FALSE, it means that a possibility was not considered and not included in the formula, or it means that the last possibility was not given a value or text in the value_if_false argument.

Try this video:

Highline Excel Class 35: IF Function Vs. VLOOKUP Function

This video shows how to build the IF so that you don't leave out possibilities.

[62Dragonlady]

I used the more then 7 nested if function in 2003 and it works. When I use more then 64 nested if function in 2007 it does not come back with information all though no error comes up. Would you let me know if in fact using more then 64 nested if with the & will work.

[ExcelIsFun]

More than 7 in Excel 2003, that is the 1st time I have heard of that. As for more than 64 by using the ampersand, I have not tried it, but if you did not get an error message that says you have exceeded the limit or that you have too many characters for the cell, it sounds like it is working.

[62Dragonlady]

I have been using nested if but I can not go over 64, A vlookup will not work as the data I'm trying to get always changes columns. The worksheet with the formulas looks to different sheets to see where my inventory is and it changes all the time. It changes users, locations in the warehouse and the country. My work sheet collecting data has to be able to track these changes, right now I do it all manually to much work.

[ExcelIsFun]

You can try VBA. But I do not know VBA very well. Try posting question to Mr Excel Message Board:

mrexcel[dot]com/forum

[nobull89]

I hope this guy is getting paid for his Excel skills

[ExcelIsFun]

Dear nobull89,

I get paid in my job, but I do this for free! I hope you found some use from it!

--excelisfun

[clutchstl]

You have no idea, you just saved my ass.

[ExcelIsFun]

Dear clutchstl,

I am glad that the video was useful!

--excelisfun

[PrincessWithSkills]

You are unbelievable! There are people who work and play with Excel hours a day for years who don't know your stuff!

[ExcelIsFun]

Dear PrincessWithSkills,

If you think that I am unbelievable, the People at the Mr Excel Message Board are unbelievable*1000!!! I am just a guy having fun with Excel who is happy that you find these videos useful!

--excelisfun