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



## Jonmo1 (Jan 4, 2007)

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


----------



## Oaktree (Jan 4, 2007)

To be fair, you could just use =CODE(UPPER(A1))-64 for that mapping 

To circumvent the 7 nested IFs limitation, the other common workaround is to use multiple cells.  For example, put the first 6 IFs in B1 and have the 7th say "else give me B2" then have 6 IFs in B2 and have B2's 7th say "else give me B3" etc.

_Edit: corrected 70 to 64 (a bit late, per jonmo's note below)_


----------



## Jonmo1 (Jan 4, 2007)

Although that is pretty cool, but to be fair again, I was only using the 1=a 2=b 3=c as an example.

When I use that, I enter A in A1, it results in -5 ??

if I change formula to 

=CODE(UPPER(A1))-64 

it returns 1 for A 2 for B.


----------



## Jonmo1 (Jan 4, 2007)

And I did just find another thing to consider when using this...

the formula is technically 2 (or more) nested if Formulas, with concatenate combining the result of each into 1 text string.  That is why you want to be sure to use "" in the 7th false of each nested if.

when using this formula, be carefull arranging the ifs so that it is not possible for BOTH nested IF's (the one to the left of & and the one to the right of &) to return TRUE.

example :


```
=--(IF(A1>100,100,IF(A1>90,90,""))&IF(A1>80,80,IF(A1>70,70,"")))
```

in the interest of simplicity, those are only 2 double ifs...but you should get the idea.

in the example, any number over 90 would return TRUE for BOTH If Statements.  Therfor resulting in 10080 for a number over 100, 9080 for a number >90 but <=100


this is still valid formula for doing more than 7 ifs, just keep in mind how it works, think logically, and you should be able to manage.


----------



## ashish_gr3at (Jan 10, 2007)

That is great and wrking fine..

Bt I think we can easily solve this kind of problem through *select case *.
 
Rgds,


----------



## PaddyD (Jan 10, 2007)

"And it's practically unlimited..."

Up to the 1,024 character limit for formula text, though if you get that far you've got other problems!


----------



## SydneyGeek (Jan 11, 2007)

> Up to the 1,024 character limit for formula text, though if you get that far you've got other problems!



 Wrote one of those once. It took about 4 hours to debug, and I was never game to change it. 

Denis


----------



## ggoose2448 (Feb 20, 2007)

THis is the formula i put in the cell, and when i enter in a number all i get the correct reference i am looking for but it also adds the word "blank" at the end. How can i get rid of that?


=(IF(E3="00",$A$3,IF(E3="01",$A$4,IF(E3="1",$A$5,IF(E3="2",$A$6,IF(E3="4",$A$7,IF(E3="5",$A$8,IF(E3="6",$A$9,IF(E3="07",$A$10,""))))))))&IF(E3="7",$A$11,IF(E3="8",$A$12,IF(E3="9",$A$13,IF(E3="10",$A$14,IF(E3="11",$A$15,IF(E3="12",$A$16,IF(E3="13",$A$17,IF(E3="14",$A$18,""))))))))&IF(E3="15",$A$19,IF(E3="16",$A$20,IF(E3="17",$A$21,IF(E3="18",$A$22,IF(E3="19",$A$23,IF(E3="20",$A$24,IF(E3="21",$A$25,IF(E3="22",$A$26,""))))))))&IF(E3="24",$A$27,IF(E3="25",$A$28,IF(E3="26",$A$29,IF(E3="29",$A$30,IF(E3="31",$A$31,IF(E3="36",$A$32,IF(E3="38",$A$33,IF(E3="40",$A$34,""))))))))&IF(E3="41",$A$35,IF(E3="42",$A$36,IF(E3="43",$A$37,IF(E3="44",$A$38,IF(E3="45",$A$39,IF(E3="48",$A$40,IF(E3="55",$A$41,"")))))))&IF(E3="66",$A$42,IF(E3="83",$A$43,IF(E3="84",$A$44,IF(E3="88",$A$45,IF(E3="96",$A$46,IF(E3="99",$A$47,"blank")))))))


----------



## SydneyGeek (Feb 20, 2007)

You can get rid of the "blank" by trying a simpler approach   

Do this instead: Go to an unused column (Say, J) and, in J3:J47, put the values you inserted into the formula. Make sure to format the column as Text first...

Then your formula becomes 

```
=INDEX(A3:A47,MATCH(E3,J3:J47,0),1)
```

Denis


----------



## Jonmo1 (Feb 21, 2007)

ggoose2448,

Just remove the word blank (not the quotes around the word, just the word) from your formula...

remember, this method of unlimited nested ifs, is just multiple nested ifs concatenated together...

so yours is 7 nested ifs...

result is 

result of first nested if & result of 2nd & result of 3rd & result of 4th & result of 5th & result of 6th & result of 7th

each section (sepreated by &) is it's own formula...
think of it as if you had put 7 nested ifs into 7 different cells, say
A1 A2 A3 A4 A5 A6 and A7

then in A8 you put

=A1&A2&A3&A4&A5&A6&A7

Does that help.?


----------



## Jonmo1 (Jan 4, 2007)

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 (Feb 24, 2007)

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 (Feb 24, 2007)

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.


----------

