• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup within a range multiple selection

Mikexcel

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

Attachments

  • freight rates.xlsx
    11 KB · Views: 8
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
 
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
 
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
 
Thanks but it does not take the second variable into account (being size L)
Need both factors to be included.
 
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))))
 
Back
Top