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

#### karloqs

##### New Member
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

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### mart37

##### Well-known Member
Maybe you can use this example:

Map2
ABCDE
1sizepricesize0,25
2110price3
30,99
40,88
50,77
60,66
70,55
80,44
90,33
100,22
110,11
Cell Formulas
RangeFormula
E2E2=INDEX(B2:B11,MATCH(E1,A2:A11,-1))

#### karloqs

##### New Member
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

Replies
2
Views
106
Replies
1
Views
167
Replies
1
Views
98
Replies
3
Views
90
Replies
7
Views
158

1,127,121
Messages
5,622,861
Members
415,935
Latest member
kes1973

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back