Basic lookup formula on the next greatest value in a range

Hello everyone,

I am struggling to come up with the sumifs/index formula to pull a simple rate out of a table. I am looking for the formula to calculate the rate based on the weight input, and then to look up the next highest weight that would cover that rate. In the example attached I have different weights in kilos, which are for inputs up to that weight, anything over would go to the next weight rate.

In cell D4 of the example I have entered a 0.55 kg weight and I would like the formula to lookup the next or equal weight, in this case it would be the 0.75 kilo rate. If D4 was exactly 0.5 kg then I would want it to pull the 0.5 kg weight.

I know it should be a straightforward formula but I can't seem to figure it out.

Thanks a bunch!

Thanks for the replies guys. It seems this doesn't work for weights between 0-0.25kg

I propose such a formula
=ИНДЕКС(B3:B16;ПОИСКПОЗ(АГРЕГАТ(15;6;A3:A16/(A3:A16>D4);1);A3:A16;0))

Try

Please Login or Register  to view this content.

Another add, put this on E4 and ENTERED all together CTRL+SHIFT+ENTER because this array formula:

=INDEX(B3:B16,MATCH(MIN(ABS(A3:A16-D4)),ABS(A3:A16-D4),0))

@azumi
albardit18 need to get exact match value or the next higher value, not the closest value.
For example, D4=1.26 then the return value must be $20.75

@congnt92, that's what my formula does, just try it