#### stoutbn

##### Board Regular
I did not know how to title what I am trying to do without showing the data directly. I am trying to use a formula to choose a container code based on the dimensions of a container. This is currently a manual process which takes forever. The data below should give an explanation of my criteria and choices.

 Description Dimensions Code Printed on Container Tag Cardboard box / pallet combination Equal (or not to exceed) 48" x 34" x 27" 121 Cardboard box Equal (or not to exceed) 32" X 30" X 25" 126 Cardboard box / pallet combination Equal (or not to exceed) 48" x 39" x 34" 131
<colgroup><col width="305" style="width: 229pt; mso-width-source: userset; mso-width-alt: 11154;"> <col width="190" style="width: 143pt; mso-width-source: userset; mso-width-alt: 6948;"> <col width="109" style="width: 82pt; mso-width-source: userset; mso-width-alt: 3986;"> <tbody> </tbody>

These are just 3 of my choices. In this scenario a container that is 45" x 32" x 25" would classify as a 121 because its length, width, and height do not exceed the dimensions in the 2nd column. All of my length, width, and height options are below. The data being measured would be 3 cells (a length, a width, a height). All of my lengths, widths, and heights available are below. Please help!

 Lengths Widths Heights 5.5 5.5 5 11 7 6 12 11 7 24 15 8.375 32 22 9 38 30 11 48 32 25 52 32.5 27 54 38 32.5 64 39 34 70 45 36 72 48 39 108 52 43 44 48

<tbody>
</tbody><colgroup><col><col><col></colgroup>

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### kieran

##### Active Member
G'day stoutbn,

If you arrange the data so that the height, width and length are in columns A to C, with the code number in column D then the following formula will spit out the smallest matching code combination.
A few amendments will be necessary to the formula though.

Change references to D17 to be equal to the cell location of the last code number. (there are 3 places you need to fix it.)
Cells G3, H3 and I3 are input cells for the formula, corresponding to length, width & height. You will need to use these cells to enter the dimensions you are checking for.

Put the formula in an empty cell somewhere near G3, H3 and I3 for ease of use.

=MAX(VLOOKUP(G3,A2:D17,4,TRUE),VLOOKUP(H3,B2:D17,3,TRUE), VLOOKUP(I3,C2:D17,2,TRUE))

WARNING : if the size is too small, a #N/A error will result, If the size is too big, the lat code number is returned. (I suggest you add a dummy/warning row at the end of the list with slightly larger dimensions and a code number that is obviously weird - eg 9999

#### stoutbn

##### Board Regular
Kieran,

Thanks for the response! I like your strategy, but unfortunately will have to have a formula that works when the inputs (length width or height) do not match any of my length width height limits. For example a 31" x 30" x 24" would classify with the container code that matches a "Does not exceed 32" x 30" x 25". This is because containers will come to us in a variety of sizes, so if any of the dimensions exceeds one of those limits, then it gets kicked up to the next set of limits and respective container code. Hope this makes sense!

Maybe I need to use buckets similar to how a histogram works? Not sure how to work that in to a formula though.

#### Rick Rothstein

##### MrExcel MVP
 Description Dimensions Code Printed on Container Tag Cardboard box / pallet combination Equal (or not to exceed) 48" x 34" x 27" 121 Cardboard box Equal (or not to exceed) 32" X 30" X 25" 126 Cardboard box / pallet combination Equal (or not to exceed) 48" x 39" x 34" 131

<tbody>
</tbody>
Some questions...

1) Do you have the above table (expanded for all your possible size combinations and container codes) located on a worksheet? If so, what is the name of that worksheet and what cells are they in?

2) On what worksheet is your "raw data" (the data that you want to find the container codes for) and in what cells?

3) What does your "raw data" look like? Is it in three columns (one for length, one for width and one for height) or is it presented as in your above dimensions column (that is, a text string that contains numbers, inch symbols and X's)?

4) Can you make use of a VBA macro or UDF (user defined function in VBA) solution?

Last edited:

#### stoutbn

##### Board Regular
Rick,

1) Below is the container code guide I am to use. Should give in full detail the options I have. Generically, they begin in columns A-D and go down 53 cells. The name of the worksheet would be "Container Codes".

2) The raw data will be constantly added to a worksheet called "New Data" and will be in columns Container Length, Container Width, Container Height. I will also have Container material where steel, plastic, cardboard are determined as well as container type where expendable/returnable are determined.

3) Raw data will simply be numbers not strings.

4) I would be happy with a macro or formula. Whichever is easiest!

 Description Dimensions Code Printed on Container Tag Type Corporate owned plastic container (plastic half tub) 38" x 32" x 23" 100 Plastic Corporate owned plastic pallet ( 48X45 PT Pallet ) 48" x 45" x 6" 102 Plastic Corporate owned half standard steel tote 32.5" x 38" x 27" 120 Steel Cardboard box / pallet combination Equal (or not to exceed) 34" x 48" x 27" 121 Cardboard Plastic Knock Down Bulk Container 32" X 30" X 25" 125 Plastic Cardboard box Equal (or not to exceed) 30" X 32" X 25" 126 Cardboard Corporate owned standard steel skid Pallet dimension - 32" x 42" x 8.375" Load height equal (or not to exceed) 27" 127 Steel Plastic Knock Down Bulk Container 32" x 30" x 34" 128 Plastic Corporate owned standard steel tote 32.5" x 38" x 39" 130 Steel Cardboard box / pallet combination Equal (or not to exceed) 34" x 48" x 39" 131 Cardboard Corporate owned standard steel skid Pallet dimension 32" x 42" x 8.375" Load height equal (or not to exceed) 39" 139 Steel Corporate owned jumbo standard steel tote 43" x 45" x 48" 140 Steel Cardboard box / pallet combination Equal (or not to exceed) 45" x 48" x 48" 141 Cardboard Plastic Knock Down Bulk Container 48" x 45" x 25" 144 Plastic Plastic Knock Down Bulk Container 48" X 45" X 34" 145 Plastic Cardboard box Equal (or not to exceed) 48" X 45" X 34" 146 Cardboard Corporate owned standard tiering rack 39" x 42" x 39" 150 Steel Cardboard box / pallet combination Equal (or not to exceed) 45" x 48" x 39" 160 Cardboard Plastic Knock Down Bulk Container 64" X 48" X 44" 165 Plastic Cardboard box / pallet combination Equal (or not to exceed) 45" x 48" x 27" 170 Cardboard Plastic Knock Down Bulk Container 70" X 48" X 25" 175 Plastic Cardboard box / pallet combination Equal (or not to exceed) Greater than 48" x 45" 180 Cardboard Plastic Knock Down Bulk Container 70" X 48" X 44" 185 Plastic returnable container 1 warehouse spaces (36"x48x52") or smaller 510 Custom Returnable returnable container 1.5 warehouse spaces (54"x48x52") or smaller 515 Custom Returnable returnable container 2 warehouse spaces (72"x48x52") or smaller 520 Custom Returnable returnable container 3 warehouse spaces (108"x48"x52" or smaller) 530 Custom Returnable returnable container - Yard Varies by component 550 Custom Returnable Small 11x4 Film Contianer 4 1/8" x 10 7/8" x 4" 601 Plastic Medium 11x11 Film Contianer 11"X11"X5" 602 Plastic Plastic Flow rack container 12" X 7" X 5" 700 Plastic Cardboard box Equal (or not to exceed) 12" X 7" X 5" 701 Cardboard Plastic Flow rack container 12" X 15" X 5" 710 Plastic Cardboard box Equal (or not to exceed) 12" X 15" X 5" 711 Cardboard Plastic Flow rack container 12" X 15" X 7" 715 Returnable Cardboard box Equal (or not to exceed) 12" X 15" X 7" 716 Expendable Plastic Flow rack container 12" X 15" X 9" 720 Plastic Cardboard box Equal (or not to exceed) 12" X 15" X 9" 721 Cardboard Plastic Flow rack container 24" X 15" X 5" 725 Plastic Cardboard box Equal (or not to exceed) 24" X 15" X 5" 726 Cardboard Plastic Flow rack container 24" X 15" X 9" 730 Plastic Cardboard box Equal (or not to exceed) 24" X 15" X 9" 731 Cardboard Plastic Flow rack container 24" X 22" X 7" 735 Plastic Cardboard box Equal (or not to exceed) 24" X 22" X 7" 736 Cardboard Plastic Flow rack container 24" X 22" X 9" 740 Plastic Cardboard box Equal (or not to exceed) 24" X 22" X 9" 741 Cardboard Plastic Rack container 48" x 15" x 11" 770 Plastic Cardboard box (hand deliver only) Exceeds all other flow rack box sizes Exceeds 24" x 15" x 7" 800 Cardboard Cardboard box Equal (or not to exceed) 5.5" x 5.5" x 5" 809 Cardboard Cardboard box Equal (or not to exceed) 5.5" x 11"x 5" 810 Cardboard Cardboard box Equal (or not to exceed) 11 X 11 X 5 PREPACK BOX 811 Cardboard Cardboard box Equal (or not to exceed) 5.5" x 22" x 5" 911 Cardboard
<colgroup><col width="305" style="width: 229pt; mso-width-source: userset; mso-width-alt: 11154;"> <col width="190" style="width: 143pt; mso-width-source: userset; mso-width-alt: 6948;"> <col width="109" style="width: 82pt; mso-width-source: userset; mso-width-alt: 3986;"> <col width="128" style="width: 96pt; mso-width-source: userset; mso-width-alt: 4681;"> <tbody> </tbody>

#### Rick Rothstein

##### MrExcel MVP
Well, that table of container choices is different than you led on it would be in your first message, that is for sure (any formula or solution anyone might have developed for you based on it would have been worthless). And that table raises several questions as well...

1) Does your Dimensions column actually look the way you showed it? One entry (PREPACK BOX) has no inch-mark symbols attached to its dimension numbers whereas all the rest do? Some of the X delimiters are upper case, some lower case, some have surrounding spaces and some do not, and some have additional text whereas most do not?

2) What does Exceeds 24" x 15" x 7" mean? Your initial message said you wanted the find the dimensions which basically are minimally greater than your individual raw data dimensions... how does that "exceeds" figure in?

3) For an entry like this, Pallet dimension - 32" x 42" x 8.375" Load height equal (or not to exceed) 27", are we supposed to ignore the 8.375" number and use 27" instead?

4) How is this one, Greater than 48" x 45" supposed to be worked into the calculations (it has only two dimensions, not three)?

5) The material shown in the "Type" column does not figure into the calculations at all? In other words, you want the best fitting dimensions no matter if that container is Cardboard, Plastic, Steel, Returnable, Expendable or Custom Returnable?

6) You have one Dimension marks as Varies by component, I presume that one is to be ignored?

I may have missed some questions that might need to be asked, but the above would need to be answered before anyone would be able to start to decide if the kind of solution that this forum is designed to provide (free as opposed to time-consuming requiring professional, paid consultation). I would also note that the table you provide would have to be reworked into a usable format (should be doable once you answer the above questions).

#### stoutbn

##### Board Regular
Rick,

Thank you for taking the time to look at this. The guide I just provided was meant to give a representation of the instructions I have before me, not necessarily the cells that have to be used in this solution to my problem. (I can modify the texts in the guide as much as I want, but the numbers in inches are what I have to stick to.)

I would rather spend less time worrying about the Container Material type and more on the dimension ranges I am trying to fit into. Those are my main constraints and I can figure out the container material later, hopefully with little issue.

So to answer your numbered questions. I would worry less about how those texts are represented since they can be modified, and spend more time focusing on how multiple ranges of dimensions minimums and maxes can be worked with. Hope that helps answer some of your questions.

#### shg

##### MrExcel MVP
If your container dimensions are disposed in three columns, it's easy to write a formula to select which container a given package fits within.

#### Rick Rothstein

##### MrExcel MVP
Rick,

Thank you for taking the time to look at this. The guide I just provided was meant to give a representation of the instructions I have before me, not necessarily the cells that have to be used in this solution to my problem. (I can modify the texts in the guide as much as I want, but the numbers in inches are what I have to stick to.)

I would rather spend less time worrying about the Container Material type and more on the dimension ranges I am trying to fit into. Those are my main constraints and I can figure out the container material later, hopefully with little issue.

So to answer your numbered questions. I would worry less about how those texts are represented since they can be modified, and spend more time focusing on how multiple ranges of dimensions minimums and maxes can be worked with. Hope that helps answer some of your questions.

I think questions 2, 3, 4 and 6 still need to be addressed (you may know what they mean, but to someone not knowledgeable in your business model (like me), they are kind of nebulous specifications.

#### stoutbn

##### Board Regular
Shg,

They will be in three separate columns like you are suggesting. Please expand more if you can, thanks!

Replies
3
Views
689
Replies
1
Views
2K
Replies
5
Views
460
Replies
5
Views
958
Replies
4
Views
3K

1,191,025
Messages
5,984,197
Members
439,877
Latest member
kellylet

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back