larger return value for vlookup??

[forgotten025]

hi... i was wondering if u cud do the opposite of vlookup... wat i mean is how instead of being able to have a table of values and getting the value just smaller than the lookup value, is it possible to get the value just bigger...?

my table has a list (not an array) of cross-sectional areas which is the first column, and i want it to return the dimensions (which includes text - eg "123 x 45"), and my lookup value is a calculated cross-sectional area, but i want the dimensions of the cross-sectional area just bigger than the one i calculated...

i hope that made sense... i don't need vlookup to do the job... anything will do...? cud anyone help?? and get back to me asap...???

 

[Lewiy]

Will something like this work for you (the values would obviously have to be sorted in ascending order):

Excel Workbook
	A	B	C	D	E
1	Lookup table		Value to Find		Formula
2	10		55		60
3	20
4	30
5	40
6	50
7	60
8	70
9	80
10	90
11	100
Sheet3

 

[forgotten025]

i tried it but it didn't work... my lookup table does have more than one column... and i want the returned value to come from another column... and for the match function it says the lookup table needs to be an array... mine isn't... thanks for the reply though...

 

[Lewiy]

Ok, try this amendment to return the value from column B:

=INDEX(A:B,MATCH(C2,A:A,1)+1,2)

 

[forgotten025]

oh basically wat i want it to do is wat a matchtype of -1 will do... "finds the smallest value that is greater than or equal to lookup_value"....

 

[Peter_SSs]

forgotten025

Welcome to the Mrexcel board!

Could you provide a small set of sample data and expected result? Give us an idea of the layout etc.

 

[forgotten025]

omg it worked..!! thank u thank u thank u... u have no idea how long i've tried to get excel to do that... nor how long i've searched for something on the net.... even my lecturer (who set this assignment) doesn't know how to do it... lol... thank u thank u thank u...