Finding which of the 4 boxes best hold the dimensi

问题:

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 would 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.

This chart is an example of how I kinda have things set up.

Can anyone help me out with this?

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

回答1:

I've switched item 4444 length and width (so always length >= width).

for a simple "first box to fit in" you can use:

=INDEX(A$2:A$5,MATCH(1,(B$2:B$5>=F2)*(C$2:C$5>=G2)*(D$2:D$5>=H2),0))

to get the "box with the least waste of space" you can use:

=INDEX(A$2:A$5,MATCH(MIN(IF((B$2:B$5>=F2)*(C$2:C$5>=G2)*(D$2:D$5>=H2),B$2:B$5*C$2:C$5*D$2:D$5-PRODUCT(F2:H2),1E+100)),IF((B$2:B$5>=F2)*(C$2:C$5>=G2)*(D$2:D$5>=H2),B$2:B$5*C$2:C$5*D$2:D$5-PRODUCT(F2:H2),1E+100),0))

This are array formulas and need to be confirmed with CTRL+SHIFT+ENTER!

The table to the right is just for showing the unused space and is not used
All formulas are written for I2 and can be copied down

EDIT

without sorting and the case where no box fits, it gets a bit complex and looks like this:

=IFERROR(INDEX(A$2:A$5,MATCH(MIN(IF(((B$2:B$5>=G2)*(C$2:C$5>=H2)+(B$2:B$5>=H2)*(C$2:C$5>=G2))*(D$2:D$5>=I2),B$2:B$5*C$2:C$5*D$2:D$5,1E+100),1E+99),IF(((B$2:B$5>=G2)*(C$2:C$5>=H2)+(B$2:B$5>=H2)*(C$2:C$5>=G2))*(D$2:D$5>=I2),B$2:B$5*C$2:C$5*D$2:D$5,1E+100),0)),"")

But if you can set the order of the boxes from smallest volume to largest, then this will do:

=IFERROR(INDEX(A$2:A$5,MATCH(TRUE,((B$2:B$5>=G2)*(C$2:C$5>=H2)+(B$2:B$5>=H2)*(C$2:C$5>=G2))*(D$2:D$5>=I2)>0,0)),"")

And if you set the order of the boxes from smallest volume to largest while also having always length >= width, then this will do:

=IFERROR(INDEX(A$2:A$5,MATCH(1,(B$2:B$5>=G2)*(C$2:C$5>=H2)*(D$2:D$5>=I2),0)),"")