the smallest value that is greater than or equal to lookup_value
the smallest value that is greater than or equal to lookup_value
(OP)
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,





RE: the smallest value that is greater than or equal to lookup_value
multiply the range values by -1 (use negative numbers) for the VLOOKUP
RE: the smallest value that is greater than or equal to lookup_value
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!
RE: the smallest value that is greater than or equal to lookup_value
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
RE: the smallest value that is greater than or equal to lookup_value
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
RE: the smallest value that is greater than or equal to lookup_value
You both really know Excel inside and out.
cheers,
RE: the smallest value that is greater than or equal to lookup_value
(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.