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


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"campka" wrote in
message ...

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),I f($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$2 6,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),I f($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+(K6582)-23))/12,1)))),0)),0,VLOOKUP($M65,Detail!$O$5:$X$26,MIN( 10,(MAX(2,2+CEILING(MAX(0,(K65+(K6582)-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...o&userid=37533
View this thread: http://www.excelforum.com/showthread...hreadid=571749