SOLVED

Help with Complex Lookup using Lenght x Width x Height and Weight to Find Costs.

Copper Contributor

Help Excel Wizards!

 

I am trying to find a formula that can pull fulfillment costs/fees from two tables for a product based on dimensions in inches (LxWxH) and weight. One table is fees for Hazmat items and one table is for non-Hazmat items. I would like to be able to enter the product dimensions in inches (LxWxH), the weight in lbs and whether it is Hazmat or not (YES/NO) in the table located in Columns A & B (See attached). Then have the Flat Fee from Column M returned in cell K3. If you can point me in the right direction with just that, you will be a blessing!

However, I have also highlighted some rows in Orange that I could use help with. These rows have additional instructions to calculate the fees. Along with the flat fee (Column M) products that fall within these Tiers have a max weight (Column K) where the flat fee can be applied. If the item exceeds that Tier's Max Weight (Column K), then there is an additional cost per pound (Column L) on top of the Flat Fee (Column M). 

I feel like I'm asking for a lot here or maybe it's easier than I think but I'm lost and I'd appreciate any advice or feedback. Thank you in advanced!

3 Replies
best response confirmed by Brian175 (Copper Contributor)
Solution

@Brian175 See attached, cells L3:Q3. I assumed each column is looked up independently and that subsequently the highest row is needed to take the price from.

@Jan Karel Pieterse This helps a ton! I'm going to play around with your method and see what I can come up with. That method seems like it will work but the way my table is set up is causing some issues. I will get back to you if I figure it out. Thank you! 

@Jan Karel Pieterse Figured it out with your help. Thank you!

For anyone who sells online particularly amazon, this spreadsheet helps calculate your fulfillment fees for private label products.

1 best response

Accepted Solutions
best response confirmed by Brian175 (Copper Contributor)
Solution

@Brian175 See attached, cells L3:Q3. I assumed each column is looked up independently and that subsequently the highest row is needed to take the price from.

View solution in original post