the smallest value that is greater than or equal to lookup_value

[josephv]

Good day, how can one get the smallest value that is greater than or equal to a lookup_value?

For example if the lookup value was 150 and the range was:

010, 100, 200, 300, 400

I would like to choose 200, since it is greater than 150 and is smaller than the 300 and 400.

The problem with VLOOKUP is that it chooses the largest value that is lesser than or equal to a lookup_value (i.e. it would choose 100 in this case).

This is a common problem in engineering, since one typically chooses some equipment (e.g. ballast, pump, fan) that is just greater than the rating (or calculated value).

We would like to use Excel functions and avoid macros (VBA) if possible.

Thanks,

 

[josephv]

OK, just figured out something that might work...

multiply the range values by -1 (use negative numbers) for the VLOOKUP

 

[yakpol]

josephy,
Combination of INDEX and MATCH functions gives more flexibility than LOOKUP.
Suppose 010 100 200 300 400 is stored in A1:E1
input value 150 is stored in A2 then the following formula will return position of number >= 150 within a range:
=MATCH(A2-0.001,A1:E1,1)+1 returns 3 in your case
=INDEX(A1:E1,MATCH(A2-0.001,A1:E1,1)+1)) returns 200

hope it helps!

 

[yogia]

Hi Josephv:

Using the layout as in Yakpol's post, here are couple of other formulations that should work for you ...

1) formula ... =INDEX(A1:E1,INDEX(MATCH(1,1/(A1:E1>=A2),0),0))

2) array formula ... =MIN(IF(ISNUMBER(1/(A1:E1>=A2)),A1:E1))

3) array formula ... =LOOKUP(INDEX(A1:E1,MATCH(1,1/(A1:E1>=A2),0)),A1:E1)

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[yogia]

Hi josephv:

continuing with my last post, may array formulas ...

array formula ... =MIN(IF(ISNUMBER(1/(A1:E1>=A2)),A1:E1))

and

array formula ... =LOOKUP(INDEX(A1:E1,MATCH(1,1/(A1:E1>=A2),0)),A1:E1)

will give the correct result even if the entries in cells A1:E1 were not in ascending order.


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[josephv]

Thank you, Yakpol and Yogi!

You both really know Excel inside and out.

cheers,

 

[VikingEngineer]

I have encountered this problem many times before in spreadsheets. My simple but not very elegant solution is as follows:
(i) Enter numbers 10, 100, 200, 300 and 400 in cells A2:A6
(ii) Enter these same numbers in cells B1:B5
(iii) Enter number 150 in say cell C2
(iv) Then use the following =vlookup(c2,A1:B6,2) the number returned is 200.
I hope that this helps.