How to calculate Shipping cost based on UPS charges

Hi All,

I have to charge a specific amount for shipping, and I have been provided all of UPS freight charges from a supplier I am using, which looks like this:

If the product price is between ($1.00 and $49.99), the shipping rate is $8.85
If the product price is between ($50.00 and $74.99), the shipping rate is $10.85

This product price range variance goes all the way to $2000.00

When I have a spreadsheet with over 5000 products, with various prices, how best can I formulate a cell to tell me how much shipping would be for each product? I tried the IF function, but there would be over 30 IF's in that formula. I have an online store, and it would be really time consuming to add a different percentage to each product, to cover for the shipping. There are apps that assign pricing for you, but they normally have one place to enter your markup, for items such as shipping. So if I add 10% to one product, to cover for the right shipping amount, the next product price may be $100 difference in value. I thought of just averaging the markup for all products, ensuring that I would not be in the hole too much, but not so sure this would work with the amount of shipping charge variables this supplier has issued. Any help would be greatly appreciated. Thank you.

Hi John,

Thanks for the quick response sir. I have attached the excel file, as per your guidance. If any questions, I will be available, thank you sir.

Thanks so much John! That is a perfect fix, and I sincerely appreciate your work on that sir.