More than 7 (almost unlimited) nested ifs YES it's possible

[Jonmo1]

Yes it is possible to nest more than 7 Ifs. And it's practically unlimited, I've gone as far as 26. (just the alphabet)

1st, must give credit where it's due. Fitzhay showed this to me in this post

http://www.mrexcel.com/board2/viewtopic.php?t=250235&highlight=

Don't kow if it was his original idea or not, but that's where I got it.

How? - Concatenate

simply write your basic 7 nested if formula

=IF(A1="a",1,IF(A1="b",2,IF(A1="c",3,IF(A1="d",4,IF(A1="e",5,IF(A1="f",6,IF(A1="g",7,"")))))))

make sure to use "" in the False section of the 7th if.

then ADD another 7 nested if formula to the end of that, sepreated by &

=IF(A1="a",1,IF(A1="b",2,IF(A1="c",3,IF(A1="d",4,IF(A1="e",5,IF(A1="f",6,IF(A1="g",7,"")))))))&IF(A1="h",8,IF(A1="i",9,IF(A1="j",10,IF(A1="k",11,IF(A1="l",12,IF(A1="m",13,IF(A1="n",14,"")))))))

IMPORTANT !!!!!!!
This is technically 2 nested if statements Joined together into 1 text string by concatenate (&). It is important for you to recognize where each nested if statement begins and ends. You MUST put "" in the LAST (furthest to right) False section of EACH Nested IF Statement.
for example, in the example formula I gave, it ended like this
IF(A1="n",14)))))))
and if I typed B in A1, it will display result of 1st nested if&"FALSE" or
2FALSE

The only problem I see with this is Concatenate forces the result as text, which can be problematic if you need numbers for other dependant formulas.

But easily resolved by adding -- to the beginning

--(=IF(A1="a",1,IF(A1="b",2,IF(A1="c",3,IF(A1="d",4,IF(A1="e",5,IF(A1="f",6,IF(A1="g",7,"")))))))&IF(A1="h",8,IF(A1="i",9,IF(A1="j",10,IF(A1="k",11,IF(A1="l",12,IF(A1="m",13,IF(A1="n",14,""))))))))

Just awesome !!

 

[mikerickson]

I might be misinterpreting the issue, but


= if (iserror(match(a1,{"v1","v2",..,"vn"},0),"false",match(a1,{"v1","v2",..."vn"},0))

will compare a1 against a lot of constants, returning a different value depending on which constant is present.

Combined with CHOOSE, would this be relevant to the issue at hand?

 

[Jonmo1]

Yes, I'll totally agree there are more efficient ways to accomplish whatever you're doing with nested ifs. But I made this post strictly as an answer that YES, you can nest more than 7 Ifs. As I've watched, read and responded in this forum, How to (or need to) get around the 7 nested if limit is a very common issue presented.

I present this post simply as an easy method to nest more than 7 ifs. Once someone understands how to do nested ifs, it's very easy to understand how to make more than 7. By Simply adding the &. Some may not understand the Choose/Match or Lookup methods. I personally am barely getting a grasp on those myself, so the nested if's are easier to write/understand.