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

Code:

=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 &

Code:

=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

Code:

--(=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 :

Code:

=--(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

Code:

=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.?

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

Jonmo1

MrExcel MVP

Excel Facts

Oaktree

MrExcel MVP

Jonmo1

MrExcel MVP

Jonmo1

MrExcel MVP

ashish_gr3at

New Member

PaddyD

MrExcel MVP

SydneyGeek

MrExcel MVP

ggoose2448

New Member

SydneyGeek

MrExcel MVP

Jonmo1

MrExcel MVP

Similar threads

Forum statistics

Share this page

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

MrExcel MVP

Excel Facts

MrExcel MVP

MrExcel MVP

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock