http://www.decisionmodels.com/index.htm
--
HTH...

Jim Thomlinson


"Paul C" wrote:

Jim - The Index Function works nicely. Do you know of any good references on
volitility and calc overhead? I have some large workbooks and could probably
benefit from this information.

Paul
--
If this helps, please remember to click yes.


"Jim Thomlinson" wrote:

I would avoid the use of offset as it will make your function volatile and
increase the calculation overhead... Index would be better (IMO). I would
attack this in one of 2 ways... One is to reverse the sort order of your
lookup range and use a decending match like this...

=INDEX(L22:L44, MATCH(B9, L22:L44, -1))

The other is to use a bit more complicated formula. Essentially it works
like this. Determine if you have an exact match. If so then the lookup is
easy. If not then add 1 to your match to get the next highest value...

=IF(ISNA(MATCH(B9, L22:L44, 0)), INDEX(L22:L44, MATCH(B9, L22:L44, 1)+1),
INDEX(L22:L44, MATCH(B9, L22:L44, 1)))

--
HTH...

Jim Thomlinson


"Paul C" wrote:

Chris

Try this - There is probably a better way so I am monitering this to see if
anyone has something not as clunky. I have had this crop up and this is what
I came up with.

=OFFSET(L22,MATCH(B9,L22:L44,1)-IF(ISERROR(MATCH(B9,L22:L44,0)),0,1),0)

The MATCH(B9,L22:L44,1) give the position in the array of the closest match
less than or equal to B9. The -If(ISERROR(MATCH(B9,L22:L44,0),0,1) subtracts
1 if the match is exact.

If B9 is less than the first number in the array this will error so you may
need to add a condition for that like this =IF(B9<L22,L22,OFFSET....)

If B9 is greater than L44 then you would get 0 or whatever is in cell L45 so
if this occurs you may need another condition like this
=IF(B9<L22,L22,IF(B9L44,L44,OFFSET....))


--
If this helps, please remember to click yes.


"Chris" wrote:

I am using Vlookup to get a value. The starting cell value I have falls
between 2 different cells in my lookup. It is giving me the cell of lesser
value but I need the cell with the greater value, What is the formula for
this. I'm using =Lookup(b9,L22:L44). The value of cell B9 may
be 16533 and say L30 = 16525 and L31 = 16540. How can I get it to return cell
L31.
Thanks
Chris