Excel VLOOKUP

[siclic]

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 &quot;Area&quot;

lookup_value = 1000

VLOOKUP(1000,Area,2,True)

&quot;Area&quot; Array values 950 A
1050 B

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

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

 

[joerd]

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

 

[TrevorP]

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 D12, etc, not D.

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

 

[mgp]

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

 

[jproj]

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 &quot;B&quot;, 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

 

[TrevorP]

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...

 

[psafety]

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

 

[siclic]

Hi Guys,

Many thanks for your responses to my question. I've never used nested functions before, other than &quot;If&quot; 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 &quot;Area1&quot;
Make array 506 - L &quot;Area&quot;

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

 

[ivanlocke]

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.

 

[ivanlocke]

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,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.