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?
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
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
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
=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
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
I often due this to index before or after the true lookup value.
dmcoffman
RE: Excel VLOOKUP
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
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
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.