Select next value up in a vlookup if value does not match exactly

[karloqs]

I am trying to use a vlookup formula to select the correct price for the matching item in my range of data.
However, If the the size of the item in my data list falls between size a category in my lookup table, I want the formula to select the price from next highest row up and not nearest value if the nearest value match is lower.

If the data exceeds the lookup value, it needs to select the price from the next highest line.


e.g

See example screen shot below.

if the size from my data works out to 0.13 - I want the formula to select the price from the 0.2 price line range and not 0.1 line

Currently I am using a very simple vlookup =VLOOKUP(E4,$L$5:$M$18,2,TRUE)

My Lookup table is similar to this.

Hope all of this makes sense and thanks to anyone in advance for any advise you are able to offer.

Regards

Karl

 

[mart37]

Maybe you can use this example:

Map2
	A	B	C	D	E
1	size	price		size	0,25
2	1	10		price	3
3	0,9	9
4	0,8	8
5	0,7	7
6	0,6	6
7	0,5	5
8	0,4	4
9	0,3	3
10	0,2	2
11	0,1	1
Blad1

Cell Formulas
Range	Formula
E2	E2	=INDEX(B2:B11,MATCH(E1,A2:A11,-1))

 

[karloqs]

Thanks for this - Its worked perfectly. I had to rearrange my lookup/ index date data from highest to lowest then it all worked exactly as I hoped. Thanks so much for the reply