Workaround for nesting more than 7 statements

[campka]

Guys, I am brand new here and with a mere average knowledge of excel and
I originally had a huge formula with multiple nested statements, but
because Excel cannot nest more than 7, I needed to come up with a
workaround. The formula below is ideally what I required:


Code:
--------------------
=(If(ISERROR((If($K65<=23,VLOOKUP($M65,Detail!$O$5:$X$26,2,0),If($K65<=35,VLOOKUP($M65,Detail!$O$5:$X$26,3,0),If($K65<=47,VLOOKUP($M65,Detail!$O$5:$X$26,4,0),If($K65<=59,VLOOKUP($M65,Detail!$O$5:$X$26,5,0),If($K65<=71,VLOOKUP($M65,Detail!$O$5:$X$26,6,0),If($K65<=82,VLOOKUP($M65,Detail!$O$5:$X$26,7,0),If($K65<=94,VLOOKUP($M65,Detail!$O$5:$X$26,8,0),If($K65<=106,VLOOKUP($M65,Detail!$O$5:$X$26,9,0),If($K65<=118,VLOOKUP($M65,Detail!$O$5:$X$26,10,0)))))))))))))),0,(If($K65<=23,VLOOKUP($M65,Detail!$O$5:$X$26,2,0),If($K65<=35,VLOOKUP($M65,Detail!$O$5:$X$26,3,0),If($K65<=47,VLOOKUP($M65,Detail!$O$5:$X$26,4,0),If($K65<=59,VLOOKUP($M65,Detail!$O$5:$X$26,5,0),If($K65<=71,VLOOKUP($M65,Detail!$O$5:$X$26,6,0),If($K65<=82,VLOOKUP($M65,Detail!$O$5:$X$26,7,0,If($K65<=94,VLOOKUP($M65,Detail!$O$5:$X$26,8,0),If($K65<=106,VLOOKUP($M65,Detail!$O$5:$X$26,9,0),If($K65<=118,VLOOKUP($M65,Detail!$O$5:$X$26,10,0))))))))))))
--------------------



Now I was able to get some fantastic help on this to simplify to the
following formula which is in fact working perfectly:


Code:
--------------------
=IF(ISERROR(VLOOKUP($M65,Detail!$O$5:$X$26,MIN(10,(MAX(2,2+CEILING(MAX(0,(K65+(K65>82)-23))/12,1)))),0)),0,VLOOKUP($M65,Detail!$O$5:$X$26,MIN(10,(MAX(2,2+CEILING(MAX(0,(K65+(K65>82)-23))/12,1)))),0))
--------------------


The problem is that I am having difficulty understanding in layman's
terms what the formula actually means and although its working
perfectly, I am not confident with it and I really need to know the
workings behind it. Unfortunately the person who assisted me with this
so far is not available at the moment, so I'm throwing it out to you
guys to see if anyone can shed any light on it. I would be so so
grateful if someone could guide me on it.

My interpretation of it so far:

Vlookup the value in the table, when choosing column to bring back do
not look past column 10, do not look before column 2, instead look for
column 2 + rounded up figure (0 decimal places) for the max. of .....
here's where I hit a brick wall. And I can't seem to figure out the -23
and the /12 parts.

Also I'm wondering if I could use the Roundup formula as opposed to the
Ceiling one?

Big thanks in advance for any assistance or feedback you can give.

 

[Niek Otten]

http://www.cpearson.com/excel/nested.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|
| Guys, I am brand new here and with a mere average knowledge of excel and
| I originally had a huge formula with multiple nested statements, but
| because Excel cannot nest more than 7, I needed to come up with a
| workaround. The formula below is ideally what I required:
|
|
| Code:
| --------------------
|
=(If(ISERROR((If($K65<=23,VLOOKUP($M65,Detail!$O$5:$X$26,2,0),If($K65<=35,VLOOKUP($M65,Detail!$O$5:$X$26,3,0),If($K65<=47,VLOOKUP($M65,Detail!$O$5:$X$26,4,0),If($K65<=59,VLOOKUP($M65,Detail!$O$5:$X$26,5,0),If($K65<=71,VLOOKUP($M65,Detail!$O$5:$X$26,6,0),If($K65<=82,VLOOKUP($M65,Detail!$O$5:$X$26,7,0),If($K65<=94,VLOOKUP($M65,Detail!$O$5:$X$26,8,0),If($K65<=106,VLOOKUP($M65,Detail!$O$5:$X$26,9,0),If($K65<=118,VLOOKUP($M65,Detail!$O$5:$X$26,10,0)))))))))))))),0,(If($K65<=23,VLOOKUP($M65,Detail!$O$5:$X$26,2,0),If($K65<=35,VLOOKUP($M65,Detail!$O$5:$X$26,3,0),If($K65<=47,VLOOKUP($M65,Detail!$O$5:$X$26,4,0),If($K65<=59,VLOOKUP($M65,Detail!$O$5:$X$26,5,0),If($K65<=71,VLOOKUP($M65,Detail!$O$5:$X$26,6,0),If($K65<=82,VLOOKUP($M65,Detail!$O$5:$X$26,7,0,If($K65<=94,VLOOKUP($M65,Detail!$O$5:$X$26,8,0),If($K65<=106,VLOOKUP($M65,Detail!$O$5:$X$26,9,0),If($K65<=118,VLOOKUP($M65,Detail!$O$5:$X$26,10,0))))))))))))
| --------------------
|
|
|
| Now I was able to get some fantastic help on this to simplify to the
| following formula which is in fact working perfectly:
|
|
| Code:
| --------------------
|
=IF(ISERROR(VLOOKUP($M65,Detail!$O$5:$X$26,MIN(10,(MAX(2,2+CEILING(MAX(0,(K65+(K65>82)-23))/12,1)))),0)),0,VLOOKUP($M65,Detail!$O$5:$X$26,MIN(10,(MAX(2,2+CEILING(MAX(0,(K65+(K65>82)-23))/12,1)))),0))
| --------------------
|
|
| The problem is that I am having difficulty understanding in layman's
| terms what the formula actually means and although its working
| perfectly, I am not confident with it and I really need to know the
| workings behind it. Unfortunately the person who assisted me with this
| so far is not available at the moment, so I'm throwing it out to you
| guys to see if anyone can shed any light on it. I would be so so
| grateful if someone could guide me on it.
|
| My interpretation of it so far:
|
| Vlookup the value in the table, when choosing column to bring back do
| not look past column 10, do not look before column 2, instead look for
| column 2 + rounded up figure (0 decimal places) for the max. of .....
| here's where I hit a brick wall. And I can't seem to figure out the -23
| and the /12 parts.
|
| Also I'm wondering if I could use the Roundup formula as opposed to the
| Ceiling one?
|
| Big thanks in advance for any assistance or feedback you can give.
|
|
| --
| campka
| ------------------------------------------------------------------------
| campka's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=37533
| View this thread: http://www.excelforum.com/showthread.php?threadid=571749
|

 

[Arvi Laanemets]

Hi

Something like this (check limit values in array parameter for MATCH - maybe
you have to increase them by 1)

=IF(OR($K65>=118,ISERROR(VLOOKUP($M65,Detail!$O$5:$X$26,MATCH($K65,{0;23;35;47;59;71;82;94;106},0)+1,0))),0,VLOOKUP($M65,Detail!$O$5:$X$26,MATCH($K65,{0;23;35;47;59;71;82;94;106},0)+1,0))