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

See what to do if you must have more than 7 nested IF functions: 1) Use the Ampersand, Use different cells, buy Excel 2007, use VLOOKUP, or Use a Boolean formula.

Category:

Science & Technology

Tags:

• Excel
• Nested
• IF
• limit
• for
• function
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[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!

[ExcelIsFun]

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

[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]

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.