# 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

• 11 KB Views: 8

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

#### rahulshewale1

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

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

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