Vlookup within a range multiple selection

[Mikexcel]

Hi

I am trying to create a Vlookup to automatically calculate freight.

The issue I am having is it is not a straight freight based on 1 metric (weight) it also takes into consideration volume (L) and city (Local or national).

If the weight or the L is more than the range it will push it into the next bracket.

I have attached the file with the data and an example.

Any suggestions would be appreciated.

thanks

Michael

 

[srinidhi]

Hi,

The weight and size needs to be under the parcel size so e.g. 4L and 4kg will fit within the second tier of $6.54 / $9.74. Even though 4L is under 12L, the weight pushes it into the next bracket.

You are just looking at the L & not the weight, weight 4 Kg is above 3kg & below 5 Kg, hence it is getting pushed to the next bracket of

$ 6.75

$ 13.03

 

[Mikexcel]

Hi,

The weight and size needs to be under the parcel size so e.g. 4L and 4kg will fit within the second tier of $6.54 / $9.74. Even though 4L is under 12L, the weight pushes it into the next bracket.

You are just looking at the L & not the weight, weight 4 Kg is above 3kg & below 5 Kg, hence it is getting pushed to the next bracket of

$ 6.75

$ 13.03

Thanks - what I am saying is it needs to move into the next bracket because the 4kg is too big for the previous bracket, I am not sure how to write that into a formula though.

Thanks

 

[rahulshewale1]

Hi,

Formula Cell Cell E21:

=IF(C23="Same City",LOOKUP(C22,$C$10:$C$14,$E$10:$E$14),LOOKUP(C22,$C$10:$C$14,$F$10:$F$14))

See if is ok ?

Regard
Rahul Shewale

 

[Mikexcel]

Thanks but it does not take the second variable into account (being size L)
Need both factors to be included.

 

[Mikexcel]

I've worked it out, thanks. I inverted the numbers on the table and then wrote the following formula. I am sure there's a more succinct way of writing but this is what I have so far:
=IF(D21="Same city",INDEX(E10:E14,MIN(MATCH(D20,C10:C14,-1),MATCH(D19,D10:D14,-1))),INDEX(F10:F14,MIN(MATCH(D20,C10:C14,-1),MATCH(D19,D10:D14,-1))))