Vlookup & Index Formula - Shipping Rates?

[olimits7]

Hi,

I have a spreadsheet that lists the weight for different products in a column in ounces for orders I received in different countries.

Then I have another spreadsheet that lists the shipping rates of different countries in ounces, and the column headings are listed as A, B, C, D, E, etc...where each letter contains a group of countries where these shipping rates apply to.

And finally I have a 3rd spreadsheet that tells me which countries belong to which group letter ID. For example, Canada = A, Germany = A, USA = B, UK = B, etc...

What I'm trying to do is take the product weight (in ounces) and country name from my 1st spreadsheet and lookup the group country ID and find the shipping rate based on this country and the weight of the product.

So if in my shipping rates table I have the weight broken into < 16 ounces = $2.00, < 32 ounces = $4.00, etc...a 20 ounce product would have a $4.00 shipping rate cost.

Thank you,

olimits7

 

[borgem]

How about something like this?
A1=ProductName
A2=CountryName
A3=vlookup(A1,ProductListArray,WeightColumn#,False)
A4=if(A3<16,vlookup(A2,CountryRateArray,16ozColumn#,False),if(A3<32.....

It would probably help to name the array/list on the sheets with the product weight info and the country shipping rates in order to trim down the length of the function. You could even use a data validation list for cells A1 & A2 to prevent spelling errors from causing hiccups.

 

[olimits7]

Hi,

Thanks for your reply!

The only issue I see with this is I don't want to use the actual weight amount within the IF statement. My shipping rates table goes from 16 ounces to 2400 ounces in 1 pound increments; so this is why I was seeing if I could use a vlookup formula to look up the right shipping rate in the table based on the product weight.

Are there any other options I could use to do this?

Thank you,

olimits7

 

[borgem]

Can you post a sample of your shipping rates table?

 

[olimits7]

Shipping Rates Table:
(ozs) (lbs) A B
16 1.0 5.00 5.50
32 2.0 6.00 6.50
48 3.0 7.00 7.50
64 4.0 8.00 8.50
80 5.0 9.00 9.50
96 6.0 10.00 10.50
112 7.0 11.00 11.50
128 8.0 12.00 12.50
144 9.0 13.00 13.50
160 10.0 14.00 14.50
etc...

Country Group ID Table:
Country Group ID
USA B
UK B
Germany B
China A
Japan A
etc...

Product/Order Table:
Country Product Weight (ozs)
USA 25
UK 20
UK 12
Germany 55
etc...

Could I use an Index/Match formula?

Thank you, again!

olimits7

 

[olimits7]

Does anyone know how I could do this?

I want to be able to use a less than or equal to formula this way it finds the right shipping rate based on the product weight.

Thank you,

olimits7

 

[borgem]

I built a mock-sheet and this seemed to work. One trick is that you would have to re-sort your shipping rates by descending weight...just a catch of how the index/match works. I built it all on one page, so I will be listing the formula and then how you will have to tweak it.

C3=IFERROR(INDEX($H$2:$K$12,MATCH(B3,$H$2:$H$12,-1),MATCH(INDEX($E$1:$F$6,MATCH(A3,$E$1:$E$6,0),2),$H$2:$K$2,0)),"Error")

$H$2:$K$12 >> The entire shipping rates table (in all selections from the tables, you must include the header row/cell)

B3 >> Product/Order Table cell with the weight in it.

$H$2:$H$12 >> The first column only of your shipping rates table

$E$1:$F$6 >> The entire country/group ID table

A3 >> Product/Order Table cell with the country in it.

$E$1:$E$6 >> The first column only of the country/group ID table

$H$2:$K$2 >> The top row only of the shipping rates table

 

[borgem]

This assumes that you have a country in A3 and the product weight in B3. Once you get this working, you should be able to copy the formula down, minding that you keep the absolute references in place.

 

[olimits7]

Hi,

Works perfectly!!! Thank you so much, this makes finding the exact shipping costs a lot easier!!!

Thank you, again!

olimits7

 

[borgem]

Thanks for the feedback. This is actually my first time playing with INDEX/MATCH. I add to much of my excel knowledge base by hunting down answers for questions that are out of my scope.