Excel : Need to find a price depending on weigth

[LeBlatt]

Heres the problem, and I think it can be solved easily, but cant find a solution :
in one sheet I have a 2 column table, parcel weight and shipping rate. On another I have a list of parcels (with weight) and I need to have the rate displayed for each.

Thx for advice !

 

[xlbo]

Ok, say your parcel weight and shipping rate table are in Sheet1, range A1:B20

On sheet2, you have a list of parcels and weights in A1:B100

in C1, enter =Vlookup(B1,sheet1!$A$1:$B$20,2,false)
and then drag down.
This should bring up your shipping rate for your parcels

For better design, highlight your parcel weight and shipping rate table, goto Insert Name Define and give it a name like sRates

then your formula would be =vlookup(B1,sRate,2,false)

HTH
Geoff

 

[SkipVought]

LeBlatt,

On the sheet with Weight and shipping rate,
1. Make headings Weight, Rate
2. highlight the entire table and
3. Menu Items - Insert/Name/Create - and select the checkbox for Create names in top row.
4. sort table in descending order by weight

On the sheet with your parcels and weights, make columns...
1. ShippingParcel, ShippingWeight, ShippingCost
2. highlight the entire table and
3. Menu Items - Insert/Name/Create - and select the checkbox for Create names in top row

Then ShippingCost would be...

=IF(ISNA(INDEX(Rate,MATCH(ShippingWeight,Weight,-1),1)),INDEX(Rate,1,1),INDEX(Rate,MATCH(ShippingWeight,Weight,-1),1))*ShippingWeight

VOLA Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

xlbo,
Dont think that Vlookup will work if the Shipping Weight does not have an EXACT MATCH in the lookup table.

Thats why I used the MATCH function with the -1 match type. Skip,
metzgsk@voughtaircraft.com

 

[xlbo]

Yeh - didn't consider that - oooops - however, it won't work with FALSE as the 4th arg but should do exactly the same as Match -1 with a TRUE in the 4th arg.
Note to self - "Contexts....always consider the contexts"

Geoff