3 Criteria aligned with 3 Criteria

[chloec]

My question: I want to use formula to decide optimal box to use for each product - minimizing extra air space in box and using smallest box possible to fit product.

I also want to establish, using a formula how efficient my box packing is. So maybe need a % of how much extra space will be in each dimension and then all dimensions.

1) I have boxes w dimensions (LxWxH in mm)
Box 1 - 336 162 116
Box 2 - 311 161 108
Box 3 - 520 380 107
Box 4 - 465 360 107
Box 5 - 343 187 120
Box 6 - 325 192 120

2) I have product dimensions (LxWxH in inches)
Product 1 - 10.444 9.375 1.125
Product 2 - 10.555 10.125 2.125
Product 3 - 11.777 9.875 3.125
Product 4 - 12.888 8.313 4.125
Product 5 - 11.000 5.500 5.250

is there a formula that will select the BEST (OPTIMAL) box to put product in taking all LxWxH criteria into account?

For example:
Product 4 cannot fit in box 2 because 12.888in product length is bigger than 311 mm (12.244 inches) box length.

The examples used are ones pulled out of my head, I hope suggested formula works across a variety of box and product combinations.

Thank you for the help, excel wizards

 

[Peter Bartholomew]

The key calculation can be performed using the MINIFS function, if you have it!
The function is used to determine the minimum volume box that would accommodate each product in turn

= MINIFS( Box.Volume, Box.L, ">"&Pr.L, Box.W, ">"&Pr.W, Box.H, ">"&Pr.H )

Other more obvious parts of the calculation include the conversion of inches to mm and the calculation of the volumes for both the packaging and the product.
e.g. the box volumes in cc are given by

= ( Box.L * Box.W * Box.H ) / 1000

According to the version of Excel and personal preferences this could be a relative formula, a CSE array formula or a spilt formula.

 

[chloec]

@Peter Bartholomew - I think this worked and it's incredible. Still verifying here and there - but you made it so easy. Thank you so much. Will follow up if I have additional questions on this solve.

 

[chloec]

@Peter Bartholomew -
#1 - I have edited your spreadsheet with a new criteria. I am using Index & Match to find the box name but in the case where two of criteria have same volume, my index/match is only picking up the first instance. See attached file "package2" for illustration of this.

Is there alternative to index and match to pick the correct box when two boxes have same volume?

I am also happy to donate to you for your efforts if you want to send paypal link or something!

 

[Peter Bartholomew]

@chloec

I have attached a file with some options for displaying a box name as opposed to its volume. I have also converted the data tables into Excel Tables so that the range names become dynamic. I am not sure how easy you will find it to implement the methods you choose in your workbook.

If you need help then by all means use the conversion function to exchange contact details. The 'best' solution may depend both on your use-case and the version of Excel that you use. Office 365 is about to change radically and that would influence any recommendation I may have.