Looking for formula that show me box will fit in lockbox

[JoumesBlunt]

Hello,
Like in topic I looking for formula, which will tell me that some boxes will fit or not in lockbox.
Product dimensions can be freely mixed, as long as the formula filters whether the package will enter any locker.
Sorry for my bad english, if you dont understeand sometching please ask.

picture below
Thanks for help!!!

 

[kweaver]

Please use XL2BB to illustrate your sheet and expected results. A picture requires retyping your data.

 

[Eric W]

See if this helps:

Finding which 4 boxes can hold product X's dimensions

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

www.mrexcel.com

 

[JoumesBlunt]

Hello, i post my sheet below:

test forum.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		height	width	length	volume				length	width	height
2	Lockbox 1	8	38	64	19456			product 1	48,7	29,5	29,9	No Match
3	Lockbox 2	19	38	64	46208			product 2	39,5	29,5	43,2	No Match
4	Lock box 3	41	38	64	99712			product 3	211,4	50,6	30,9	No Match
5								product 4	58,7	37,5	33,2	No Match
6								product 5	58,7	37,5	33,2	No Match
7								product 6	49	29	26	No Match
8								product 7	77	20	18	No Match
9								product 8	39,5	19,5	39	No Match
10								product 9	15,9	14,6	12,2	Lock box 3
11								product 10	49,3	29,3	38,9	No Match
12								product 11	39,3	38	47	No Match
13								product 12	39,3	38	50,5	No Match
14								product 13	39,3	38	47	No Match
15								product 14	40	40	56,8	No Match
16								product 15	40	40	56,8	No Match
17								product 16	40	40	71,3	No Match
18								product 17	3,5	6,4	38,5	Lockbox 2
19								product 18	3,5	6,4	41,5	Lockbox 2
20								product 19	3,5	6,7	45,5	Lockbox 2
21								product 20	51,5	6,5	3,7	Lockbox 2
22								product 21	18	12	3,5	Lock box 3
23								product 22	18	12	3,8	Lock box 3
24								product 23	6	6	8,2	Lock box 3
25								product 24	6	6	8,2	Lock box 3
Arkusz1

Cell Formulas
Range	Formula
E2:E4	E2	=PRODUCT(B2:D2)
L2:L25	L2	=IFERROR(LOOKUP(2,1/(MMULT(--(SMALL(I2:K2,{1;2;3})<=$B$2:$D$4),{1;1;1})=3),$A$2:$A$4),"No Match")

Formula from erics post works but not 100% right. For example product 18 should fit in lockbox 1 but the formula for some reason pointed to lockbox 2.

Formula i use for L19 Cell is:

=IFERROR(LOOKUP(2;1/(MMULT(--(SMALL(I19:K19;{1;2;3})<=$B$2:$D$4);{1;1;1})=3);$A$2:$A$4);"No Match")

Have you got any suggestions?

 

[Eric W]

In your A1:E4 table, the boxes must be in decreasing size. Switch rows 2 and 4 and you should be ok.

 

[ExceLoki]

swapping rows 2 & 4 work on most of the rows, but mess un on products 3 & 7 as shown below. green is looking at swapped order of lockbox tables. orange is looking at original.
column M seems to be a solution, though kinda long formula.
--------------

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		height	width	length	volume				length	width	height
2	Lockbox 1	8	38	64	19456			product 1	48.7	29.5	29.9	Lockbox 3	Lockbox 3
3	Lockbox 2	19	38	64	46208			product 2	39.5	29.5	43.2	Lockbox 3	Lockbox 3
4	Lockbox 3	41	38	64	99712			product 3	211.4	50.6	30.9	Lockbox 3	no match
5								product 4	58.7	37.5	33.2	Lockbox 3	Lockbox 3
6		height	width	length	volume			product 5	58.7	37.5	33.2	Lockbox 3	Lockbox 3
7	Lockbox 3	41	38	64	99712			product 6	49	29	26	Lockbox 3	Lockbox 3
8	Lockbox 2	19	38	64	46208			product 7	77	20	18	Lockbox 3	no match
9	Lockbox 1	8	38	64	19456			product 8	39.5	19.5	39	Lockbox 3	Lockbox 3
10								product 9	15.9	14.6	12.2	Lockbox 2	Lockbox 2
11								product 10	49.3	29.3	38.9	Lockbox 3	Lockbox 3
12								product 11	39.3	38	47	Lockbox 3	Lockbox 3
13								product 12	39.3	38	50.5	Lockbox 3	Lockbox 3
14								product 13	39.3	38	47	Lockbox 3	Lockbox 3
15								product 14	40	40	56.8	No Match	no match
16								product 15	40	40	56.8	No Match	no match
17								product 16	40	40	71.3	No Match	no match
18								product 17	3.5	6.4	38.5	Lockbox 2	Lockbox 1
19								product 18	3.5	6.4	41.5	Lockbox 2	Lockbox 1
20								product 19	3.5	6.7	45.5	Lockbox 2	Lockbox 1
21								product 20	51.5	6.5	3.7	Lockbox 2	Lockbox 1
22								product 21	18	12	3.5	Lockbox 2	Lockbox 1
23								product 22	18	12	3.8	Lockbox 2	Lockbox 1
24								product 23	6	6	8.2	Lockbox 2	Lockbox 1
25								product 24	6	6	8.2	Lockbox 2	Lockbox 1
Sheet1

Cell Formulas
Range	Formula
L2:L25	L2	=IFERROR(LOOKUP(2,1/(MMULT(--(SMALL(I2:K2,{1;2;3})<=$B$7:$D$9),{1;1;1})=3),$A$7:$A$9),"No Match")
M2:M25	M2	=IFERROR(IFS(AND($B$2>=SMALL($I2:$K2,1),$C$2>=SMALL($I2:$K2,2),$D$2>=SMALL($I2:$K2,3)),$A$2,AND($B$3>=SMALL($I2:$K2,1),$C$3>=SMALL($I2:$K2,2),$D$3>=SMALL($I2:$K2,3)),$A$3,AND($C$4>=SMALL($I2:$K2,1),$B$4>=SMALL($I2:$K2,2),$D$4>=SMALL($I2:$K2,3)),$A$4),"no match")
E2:E4,E7:E9	E2	=PRODUCT(B2:D2)

 

[Eric W]

Something very weird is going on with my formula. There was another tweak needed to the table, but even with that it didn't work. I used Evaluate Formula, and there's one step that produces invalid results. I do that step by itself, and it works. ExcelLoki's version seems to have the same idea, so if it works, great. I'll poke around a bit to see if I can figure out what's going on with mine.

 

[JoumesBlunt]

On ExceLoki sheet it look like it works but in my file sometching go wrong and every row is "no match". I try to find error and i think reason is my excel version does not support function "IFS' (its on my work computer so i cant change it). On internet i find information that this formula is supported for 2019+ excel versions. Its able to change this function to other, oldest one?

test forum.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		height	width	length	volume				length	width	height
2	Lockbox 1	8	38	64	19456			product 1	48,7	29,5	29,9	Lockbox 3	no match
3	Lockbox 2	19	38	64	46208			product 2	39,5	29,5	43,2	Lockbox 3	no match
4	Lockbox 3	41	38	64	99712			product 3	211,4	50,6	30,9	Lockbox 3	no match
5								product 4	58,7	37,5	33,2	Lockbox 3	no match
6		height	width	length	volume			product 5	58,7	37,5	33,2	Lockbox 3	no match
7	Lockbox 3	41	38	64	99712			product 6	49	29	26	Lockbox 3	no match
8	Lockbox 2	19	38	64	46208			product 7	77	20	18	Lockbox 3	no match
9	Lockbox 1	8	38	64	19456			product 8	39,5	19,5	39	Lockbox 3	no match
10								product 9	15,9	14,6	12,2	Lockbox 2	no match
11								product 10	49,3	29,3	38,9	Lockbox 3	no match
12								product 11	39,3	38	47	Lockbox 3	no match
13								product 12	39,3	38	50,5	Lockbox 3	no match
14								product 13	39,3	38	47	Lockbox 3	no match
15								product 14	40	40	56,8	No Match	no match
16								product 15	40	40	56,8	No Match	no match
17								product 16	40	40	71,3	No Match	no match
18								product 17	3,5	6,4	38,5	Lockbox 2	no match
19								product 18	3,5	6,4	41,5	Lockbox 2	no match
20								product 19	3,5	6,7	45,5	Lockbox 2	no match
21								product 20	51,5	6,5	3,7	Lockbox 2	no match
22								product 21	18	12	3,5	Lockbox 2	no match
23								product 22	18	12	3,8	Lockbox 2	no match
24								product 23	6	6	8,2	Lockbox 2	no match
25								product 24	6	6	8,2	Lockbox 2	no match
WERSJA FORUM

Cell Formulas
Range	Formula
L2:L25	L2	=IFERROR(LOOKUP(2,1/(MMULT(--(SMALL(I2:K2,{1;2;3})<=$B$7:$D$9),{1;1;1})=3),$A$7:$A$9),"No Match")
M2:M25	M2	=IFERROR(IFS(AND($B$2>=SMALL($I2:$K2,1),$C$2>=SMALL($I2:$K2,2),$D$2>=SMALL($I2:$K2,3)),$A$2,AND($B$3>=SMALL($I2:$K2,1),$C$3>=SMALL($I2:$K2,2),$D$3>=SMALL($I2:$K2,3)),$A$3,AND($C$4>=SMALL($I2:$K2,1),$B$4>=SMALL($I2:$K2,2),$D$4>=SMALL($I2:$K2,3)),$A$4),"no match")
E7:E9,E2:E4	E2	=PRODUCT(B2:D2)

 

[ExceLoki]

try column n
----------------

lobbox fit formula.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		height	width	length	volume				length	width	height		ifs	if
2	Lockbox 1	8	38	64	19456			product 1	48.7	29.5	29.9	Lockbox 3	Lockbox 3	Lockbox 3
3	Lockbox 2	19	38	64	46208			product 2	39.5	29.5	43.2	Lockbox 3	Lockbox 3	Lockbox 3
4	Lockbox 3	41	38	64	99712			product 3	211.4	50.6	30.9	Lockbox 3	no match	no match
5								product 4	58.7	37.5	33.2	Lockbox 3	Lockbox 3	Lockbox 3
6		height	width	length	volume			product 5	58.7	37.5	33.2	Lockbox 3	Lockbox 3	Lockbox 3
7	Lockbox 3	41	38	64	99712			product 6	49	29	26	Lockbox 3	Lockbox 3	Lockbox 3
8	Lockbox 2	19	38	64	46208			product 7	77	20	18	Lockbox 3	no match	no match
9	Lockbox 1	8	38	64	19456			product 8	39.5	19.5	39	Lockbox 3	Lockbox 3	Lockbox 3
10								product 9	15.9	14.6	12.2	Lockbox 2	Lockbox 2	Lockbox 2
11								product 10	49.3	29.3	38.9	Lockbox 3	Lockbox 3	Lockbox 3
12								product 11	39.3	38	47	Lockbox 3	Lockbox 3	Lockbox 3
13								product 12	39.3	38	50.5	Lockbox 3	Lockbox 3	Lockbox 3
14								product 13	39.3	38	47	Lockbox 3	Lockbox 3	Lockbox 3
15								product 14	40	40	56.8	No Match	no match	no match
16								product 15	40	40	56.8	No Match	no match	no match
17								product 16	40	40	71.3	No Match	no match	no match
18								product 17	3.5	6.4	38.5	Lockbox 2	Lockbox 1	Lockbox 1
19								product 18	3.5	6.4	41.5	Lockbox 2	Lockbox 1	Lockbox 1
20								product 19	3.5	6.7	45.5	Lockbox 2	Lockbox 1	Lockbox 1
21								product 20	51.5	6.5	3.7	Lockbox 2	Lockbox 1	Lockbox 1
22								product 21	18	12	3.5	Lockbox 2	Lockbox 1	Lockbox 1
23								product 22	18	12	3.8	Lockbox 2	Lockbox 1	Lockbox 1
24								product 23	6	6	8.2	Lockbox 2	Lockbox 1	Lockbox 1
25								product 24	6	6	8.2	Lockbox 2	Lockbox 1	Lockbox 1
lockbox fit formula

Cell Formulas
Range	Formula
L2:L25	L2	=IFERROR(LOOKUP(2,1/(MMULT(--(SMALL(I2:K2,{1;2;3})<=$B$7:$D$9),{1;1;1})=3),$A$7:$A$9),"No Match")
M2:M25	M2	=IFERROR(IFS(AND($B$2>=SMALL($I2:$K2,1),$C$2>=SMALL($I2:$K2,2),$D$2>=SMALL($I2:$K2,3)),$A$2,AND($B$3>=SMALL($I2:$K2,1),$C$3>=SMALL($I2:$K2,2),$D$3>=SMALL($I2:$K2,3)),$A$3,AND($C$4>=SMALL($I2:$K2,1),$B$4>=SMALL($I2:$K2,2),$D$4>=SMALL($I2:$K2,3)),$A$4),"no match")
N2:N25	N2	=IF(AND($B$2>=SMALL($I2:$K2,1),$C$2>=SMALL($I2:$K2,2),$D$2>=SMALL($I2:$K2,3)),$A$2,IF(AND($B$3>=SMALL($I2:$K2,1),$C$3>=SMALL($I2:$K2,2),$D$3>=SMALL($I2:$K2,3)),$A$3,IF(AND($C$4>=SMALL($I2:$K2,1),$B$4>=SMALL($I2:$K2,2),$D$4>=SMALL($I2:$K2,3)),$A$4,"no match")))
E2:E4,E7:E9	E2	=PRODUCT(B2:D2)

 

[JoumesBlunt]

Now it works, thank you very much for help.