• 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

srinidhi

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

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

Mikexcel

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