Carton/box size calculator formula

Hello, I am new here, and like many people my first post is a question I have spent all day scratching my head with

I am trying to create a spreadsheet where if you put in the dimensions of a product to be sold online (Length x width x height) it will tell you which box size to use from a selection of already existing box sizes in stock.

This is for people working on a packing line in a distribution warehouse, where there is a range of 14 different box sizes, and we are trying to cut down on the amount of items going out in a box that is bigger than a more efficient sized box

The problem is that I know formulas that can do a simple lookup of "if weight is between 5.0 - 6.0kg then show "use bubble wrap"", but when it has to meet multiple criteria for length x width x height of product AND fit the correct sized box, it becomes a real head scratcher..

e.g. say a product is:
385 x 200 x 55mm

That would fit box:
Box 3: 406 x 305 x 76

BUT

If a product is:
385 x 200 x 100mm

Then it wont fit box 3 because of how tall it is (it would have to go in box 6: 406 x 305 x 102mm)


I have spent all evening trying to find some sort of calculator within excel that can do this, and have found nothing. I am also not very skilled with Excel, only very basic equations, so It may not even be possible.

The specific box sizes are:

L W H
254 178 76 Box 1: 254 x 178 x 76
330 229 102 Box 2: 330 x 229 x 102
356 254 127 Box 4: 356 x 254 x 127
356 203 178 Box 5: 356 x 203 x 178
356 305 254 Box 10: 356 x 305 x 254
406 305 76 Box 3: 406 x 305 x 76
406 305 152 Box 7: 406 x 305 x 152
457 229 216 Box 8: 457 x 229 x 216
457 356 203 Box 11: 457 x 356 x 205
483 305 102 Box 6: 483 x 305 x 102
485 330 152 Box 9: 485 x 330 x 152
559 457 305 Box 12: 457 x 568 x 305
559 457 305 Box 12: 457 x 568 x 305
559 457 305 Box 12: 457 x 568 x 305

See if this (the attachment) is what you are looking for.

Administrative Note to All Participants:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

Click on the file attached to download it.