Finding which 4 boxes can hold product X's dimensions

[mystic_muffin]

Hey, Friends;

So lets say I have 4 box sizes with their dimension in a list. I also have an array of objects to fit in the boxes with their own dimensions. I only want to fit one item in one box so i want the box that fits the item the best. Under my "box that fits" column, I'd like a formula or something that can look down the item dimensions and compare them to the box dimensions and find whatever box works best with item, then display the name from column A. So if i have an item that 9x9x9 and a box that is 10x10x10, my sheet will choose that box because the item is just less than the box size.
[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]box name[/TD]
[TD]box length[/TD]
[TD]box width[/TD]
[TD]box height[/TD]
[TD]item name[/TD]
[TD]item length[/TD]
[TD]item width[/TD]
[TD]item height[/TD]
[TD]box that fits[/TD]
[/TR]
[TR]
[TD]box one[/TD]
[TD]100[/TD]
[TD]68[/TD]
[TD]100[/TD]
[TD]1111[/TD]
[TD]90[/TD]
[TD]60[/TD]
[TD]88[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]box two[/TD]
[TD]110[/TD]
[TD]58[/TD]
[TD]43[/TD]
[TD]2222[/TD]
[TD]105[/TD]
[TD]50[/TD]
[TD]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]box three[/TD]
[TD]78[/TD]
[TD]43[/TD]
[TD]35[/TD]
[TD]3333[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]box four[/TD]
[TD]48[/TD]
[TD]43[/TD]
[TD]36[/TD]
[TD]4444[/TD]
[TD]40[/TD]
[TD]60[/TD]
[TD]90[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Can anyone help me out with this?

I appreciate any assistance. Let me know if i need to clear anything up.

 

[Eric W]

My belief is that is has to do with how you converted the formula to use your regional settings. In particular, I use commas but you use semicolons to separate parameters in the formula. However, the commas and/or semicolons within array constants should not be changed, as they indicate rows or columns. So your formula should be:

=IFERROR(LOOKUP(2;1/(MMULT(--(SMALL(I2:K2;{1,2,3})<=$B$2:$D$7);{1;1;1})=3);$A$2:$A$7);"No Match")

 

[Marcelo Branco]

It seems your Excel (regional settings) uses
; (semicolon) as argument separator
, (comma) as decimal separator

If so try to adjust Eric's formula to
=IFERROR(LOOKUP(2;1/(MMULT(--(SMALL(I2:K2;{1\2\3})<=$B$2:$D$7);{1;1;1})=3);$A$2:$A$7);"No Match")

Hope this helps

M.

 

[Marcelo Branco]

To find out rows and columns separators you can try
In an empty sheet
select C1:D2
in the formula bar type
=A1:B2
press F9
I think you'll see something like
={0\0;0\0}
that means
\ is the columns separator
; is the rows separator

M.

 

[Manu9211]

Hi @Eric W, hi @Marcelo Branco,

thank you for your quick help. It seems that the regional settings are the problem, as you have described. I tried the same adjustment as Eric before, but without success. Now with the use of / (backslashes) instead of , (commas) in the small function, it worked fine.


Thanks again to both of you for your time.

 

[Marcelo Branco]

You are welcome. Glad to help

M.

 

[Vicky_D]

Hi guys! I have faced a problem - I have not the exact size of the goods (5.75, 2.5, etc.), and this formula returns me back to No Match. What could be the problem?
Thanks in advance.

 

[Vicky_D]

Hi guys! I have faced a problem - I have not the exact size of the goods (5.75, 2.5, etc.), and this formula returns me back to No Match. What could be the problem?
Thanks in advance.

Don't pay attention to this. I have found a problem.

 

[allaahmad]

Thank you for this thread. I want to know if there is a way to calculate which box would be used if we have multiple items chosen. Launching a marketplace that allows customers to pick from a selection of items and based on their selection I want a box to be used calculated.

 

[Eric W]

Welcome to the MrExcel Forum!

First, it's usually best to start a new thread, rather than adding to one that's years old. It's possible that no one will see it.

As far as your question, I can't even imagine how to construct a formula to pick a box to handle multiple items. There are just too many ways to arrange the items. Rotate them, stack them, rearrange them. Like a 3 dimensional Tetris. I'd recommend opening a new thread, and asking if someone could write a macro to do it. I think it still might be pretty tough, but depending on your specific requirements, you might find one of the bright minds here can come up with something.

Good luck!

 

[AmbientTech]

Welcome to the MrExcel Forum!

First, it's usually best to start a new thread, rather than adding to one that's years old. It's possible that no one will see it.

As far as your question, I can't even imagine how to construct a formula to pick a box to handle multiple items. There are just too many ways to arrange the items. Rotate them, stack them, rearrange them. Like a 3 dimensional Tetris. I'd recommend opening a new thread, and asking if someone could write a macro to do it. I think it still might be pretty tough, but depending on your specific requirements, you might find one of the bright minds here can come up with something.

Good luck!

Hello @allaahmad @Eric W was there another thread based on multiple items ?