Vlookup with value between 2 cells and return greater value.

[Chris]

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

 

[Paul C]

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(B9>L44,L44,OFFSET....))

 

[Jim Thomlinson]

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

 

[sheryarkhan]

Hi!
Try
=INDEX($L$22:$L$44,MIN(IF(ABS($L$22:$L$44-B9)=MIN(ABS($L$22:$L$44-B9)),ROW($L$22:$L$44)-ROW($L$22)+1)),1)
it is an array formula so must be enter with control+shift+enter

 

[Paul C]

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

 

[Jim Thomlinson]

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

 

[Paul C]

Jim - Thank you very much - This was very informative