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