I am trying to come up with a formula that can automatically downselect to the closest box size out of a list of 20 or so box sizes with lengthxwidthxdepth as the 3 criteria.
I have been extremely close to get something working but then when i increase one of the box dimension values by 1 it screws it up again so I'm at a wits end!!!
Here is a simplified version of the table:
I'll give the table here:
ID....length.....width........Container Name....length....width
1.....600.........400.............Cont1................450........305
2.....400.........300.............Cont2................800........500
3.....200.........150.............Cont3................600........402
.......................................Cont4................250........200
Cont3
Cont1
Cont4
ID Length Width depth 1 600 400 119 2 400 300 220 3 200 150 420
Container Name length width depth Cont1 450 305 430 Cont2 800 500 135 Cont3 600 402 250 Cont4 250 200 175
This is the formula that i'm currently using. I really don't understand it, and so that is the reason as to why I don't know how to formulate an extra criterion onto this formula:
=INDEX($G$2:$G$5,MATCH(TRUE,$I$2:$I$5-$C2+$H$2:$H$5-$B2=MIN(IF($I$2:$I$5>=$C2,$I$2:$I$5-$C2,MAX($I$2:$I$5)+MAX($H$2:$H$5))+IF($H$2:$H$5>=$B2,$H$2:$H$5-$B2,MAX($I$2:$I$5)+MAX($H$2:$H$5))),0))
Thanks in advance
Julian
Bookmarks