×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

the smallest value that is greater than or equal to lookup_value

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

(OP)
OK, just figured out something that might work...

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

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!

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

(OP)
Thank you, Yakpol and Yogi!

You both really know Excel inside and out.

cheers,

RE: the smallest value that is greater than or equal to lookup_value

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources