Hi Rakesh,

Welcome to our forum. Thanks for sharing your file, however your question isn't clear, even after I added paragraph breaks to make it easier to read.

You lost me at "Once total weight...". It's not clear what you want to do, or what your desired result is, sorry.

Mynda

Hi Mynda,

Sorry for not being clear. Is their any other way to communicate  like call or video call.

Let me explain Step by step.

Tab Rate weight.

E.g.

Select A:SKu:12545455 or any sku, then D:how many max150 lbs/68 kgs: 1,2,3 or 4... ,so if they entered 1 then 1.65 x 1 =1.65(E:total weight max150 lbs/68 kgs) F:Total Weight Round off (=CEILING(J#,1) 2 lbs.

Tab Zones Zips:

Where are shipping out of A:Origin Zip 98101 to variance B: Destination zip which links to D:zone

e.g.

Ship from A:98101 to B:43443 D:Zone 5.

We have now Sku:12545455  Total Weight Round off (=CEILING(J#,1) 2 lbs and Zone 5.

Now

Tab Priority 

Need to go to this tab and look for under package weight then zone look at the cost of the shipping.

A:Weight Package 2lbs @ E:Zone 5 cost will be 29.95.

Also go through Tab Standard / Standard / 2Day / Ground.

I hope this explain help you.

So the Main Rate quote should only display as shown.

TAB Main Rate Quote

A:SKU type the sku it will display as shown.  D:how many max150 lbs/68 kgs.. 1,2,3, or 4 etc and this will each x qty, will display E and F will round off to number with is linked to TAB Rate Weight

Then G: Origin Zip, H:Destination Zip enter the zip code which will display I:Destination State, J:Zone which is linked to TAB Zones Zips to pull out information.

Now:

K:Prority Tab Cost will display the place from TAB Priority (look for A:Weight Package @ E:Zone) 

L:Standard Tab Cost will display the place from TAB Priority (look for A:Weight Package @ E:Zone) 

 same this for 2Day and Ground service.

Thank you, 

Hi Rakesh,

Thanks for clarifying. You can use lookup formulas to find the components for each item from the Rate Weight, Zone Zips, Priority, Standard, 2Day and Ground sheets.

In the attached file I've inserted VLOOKUP and INDEX & MATCH formulas on the Main Rate Quote sheet. Note: I had to convert the text numbers in column A and row 3 in the shipping cost sheets to proper numbers. There was also a space at the end of the 2Day sheet tab name that I removed. 

I hope that points you in the right direction.

Mynda

Hi Mynda,

Yes you totally got me on the right direction. I will work my original file and follow your guidance that you have given me. Sure if I get stuck I will surely message you back. Wow you we amazing and I will learn from your formula and understand and apply to my original file.

I really greatly appreciate for you support and help.

You are the best.

Thank you