×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

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!

*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

Excel VLOOKUP

Excel VLOOKUP

Excel VLOOKUP

(OP)
Hi Guys, can anyone help?

I'm using VLOOKUP in Excel 2000 to obtain a value from an array.  VLOOKUP returns a value that is <= the lookup_value.   I need the next highest value.  e.g. in the array "Area"

lookup_value = 1000

VLOOKUP(1000,Area,2,True)

"Area" Array values  950    A
                    1050    B

VLOOKUP returns 'A' but I want 'B'.

Is there a way of doing this without using a macro?

RE: Excel VLOOKUP

You can use a combination of INDEX and MATCH.

MATCH(1000, Area, 1) gives you the relative position of the largest value that is less than or equal to 1000 in range Area.

=INDEX( Area, MATCH(1000,Area,1)+1, col_num) gives you the value in range Area that follows the one found by the MATCH statement (that's where the +1 is for). You can replace col_num by the required (relative) column in Area. A value of 1 for col_num gives the value just after the value found, 2 gives the second column, etc.

in your example it would look like:
=INDEX( Area, MATCH(1000,Area,1)+1, 2)
to give  the answer 'B'

Regards,

Joerd

RE: Excel VLOOKUP

If you just want to round up, try this:
Reverse the cells so that they read downwards, i.e.
1050  B
950   A
Call the first column which contains the values you want to match your variable to, Column1, say, and the cell containing the top left hand value of this column of values, top_left.

Note that column1 should be of the form D1:D2, etc, not D:D.

Then do
=OFFSET(top_left,MATCH(1000,Column1,-1)-1,1),
Where 1000 is the value you want to roundup.

RE: Excel VLOOKUP


Try this one:

Define area 1 as your lookup column (950,1050)
Define area 2 as both the lookup column and the return values area:
950    A
1050   B


Then this nested function:
=INDEX(Area2;MATCH(1000;Area1;1);1)
Will return 950

See more in help for the lookup functions:


INDEX(array,row_num,column_num)
MATCH(lookup_value,lookup_array,match_type)


Match_type   is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.

If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.

If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

If match_type is omitted, it is assumed to be 1.



Hope this helps

Regards
Mogens

RE: Excel VLOOKUP

The method listed above works, but only if the number entered (in this case 1000) is not equal to the number in the array.  For example, if you entered 950, the formula gives you "B", which is incorrect.  Try the following formula:

=IF(Number=VLOOKUP(Number,Area,1),VLOOKUP(Number,Area,2),INDEX(Area,MATCH(Number,Num_Col,1)+1,2))

Where...
Number = the entered number (1000)
Area = your two column array
Num_Col = the first column in your array (950, 1050, ...)

Hope this helps!

jproj

RE: Excel VLOOKUP

jproj is right, match doesn't give you the answer you want if you hit it bang on, which is why I suggested to reverse the table!  Sometimes this can be a pain, especially if you have a large data set, so the formula jproj gives should work.  The advantage the method I suggested is that it will give you a minimum value, if the number entered is below the bottom of the dataset (e.g. If you wanted the next  motor size up for a pump, you would at least want to get a minimum size).  If you try putting a search value of 850 into this formula, you will get an error, but if you put it into the formula I posted above, you will get it rounded up to 950.  

A simple additional IF function to jproj's formula to give at least a minimum value will give you the same answer, if that is what you want.

Alternatively, if you don't mind reversing the dataset, the following uses INDEX rather than OFFSET, as an alternative.  

=INDEX(Area2,(MATCH(number,column1,-1)),2)

Just goes to show, there's more than one way to skin a cat...

RE: Excel VLOOKUP

Add another column in your data table.  Copy your indexing table (the column containing 950) insert it with the numbers offset by one value so the true lookup value is found adjacent the new 1050.

I often due this to index before or after the true lookup value.

dmcoffman

RE: Excel VLOOKUP

(OP)
Hi Guys,

Many thanks for your responses to my question.  I've never used nested functions before, other than "If" functions, and have never used INDEX & MATCH.  I've learned a lot from your suggestions.  The final solution, for selecting a relief valve orifice size from a table of values, was obtained by using a combination of your suggestions.

lookup_value 981 (calculated area)

Array

Area    Letter
506    H
830    J
1186    K
1841    L

Make column 506 - 1841 "Area1"
Make array 506 - L "Area"

Selected orifice letter =INDEX(Area,MATCH(981,Area1,1,1)+1,2) gives a 'K' orifice.




RE: Excel VLOOKUP

You could use an array function with vlookup.
Do an array function that gets all values greater than your taget number. Take the min of that and do your vlookup to find values in adjacent columns.

RE: Excel VLOOKUP

Whoops, I meant, get an array function that gets all values greater than OR EQUAL to your target number.

Here is what it would kind of look like. (don't have excel infront of me)
=vlookup(min(if(Z1<A:A,999999,A:A)),A:D,2,whatever option is exact match)
Remeber that it is an array equation, so you'll have to hit shift control enter or whatever it is.

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! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close