Closest match with multiple criteria (3 criteria)

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

Welcome to the forum.

Will you please attach a small sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

Instructions (Please Read Carefully):

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment (it hasn't worked for years, and despite our repeatedly asking the technical team who own the forum to fix it, they can't be bothered to do so), so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Hello Ali!

Thank you very much for the speedy response.
I will post a link to the sample workbook now.
Thanks in advance!

Julian

No - you may not post a link yet - you do not have sufficient forum privileges. Please attach the workbook as per my instructions.

Please see point #2 of my instructions, update the workbook and post again.

In B15, copied down:

=INDEX($G$11:$G$14,MATCH(1,INDEX(($H$11:$H$14>=B11)*($I$11:$I$14>=C11)*($J$11:$J$14>=D11),0),0))

Should this be Cont2 and Cont4?

Excel 2016 (Windows) 32 bit

	B	C	D
24	Depth 119 matches:		Cont2
25			Cont3

Sheet: Sheet1

I'm going to give an updated workbook Ali and Glenn.
I think i may have confused myself.
I'll just give the actual box lengthxwidthxdepth.
my mockup was not great haha

Julian

I forgot to add.... Sort the box sizes (Sm-LA) first by L, then by w then by D.

See sheet. Isn't your expected answer for the 3rd one incorrect?

Isn't your expected answer for the 3rd one incorrect?

I agree - see post #9.

Happy to explain, once we have agreed that it's giving the correct answers!!

Originally Posted by JulianS96

updated!

Please find attached new version.

If this is still a bad mockup I will implement the actual data...

Thanks in advance,

Julian

Please stop changing the attachment after a reply has been received. Following what is happening is rapidly becoming impossible.

1. Sort your table smallest to largest L, W, and then D

2. Use the formula that I have already provided.

Sorry Glenn and Ali.
I ended up confusing myself as well.
Thank you very much for all your help. I think that the solution you provided is working.
I will test it on the actual data and let you know asap.
Cheers again!
Julian

EDIT
CAN CONFIRM THE FORMULA WORKS!!!
No idea how it works but am very thankful! This has saved me hours of failed attempts (just wish i'd reached out to this forum sooner than I did)
Thank you very much Glenn for your solution and Ali for your help!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

=INDEX($G$11:$G$14,MATCH(1,INDEX(($H$11:$H$14>=B11)*($I$11:$I$14>=C11)*($J$11:$J$14>=D11),0),0))

The usual format of this formula is:

=INDEX($G$11:$G$14,MATCH(1,($H$11:$H$14>=B11)*($I$11:$I$14>=C11)*($J$11:$J$14>=D11),0))

That would be an array formula. To avoid the need to use array entry, the bit in red overcomes that... and simple enter will do the job...

So, with a sorted data table


=INDEX($G$11:$G$14,MATCH(1,($H$11:$H$14>=B11)*($I$11:$I$14>=C11)*($J$11:$J$14>=D11),0))

Red: these are the three critieria, each returning True or False. The multiplication is the equivalent of AND. TxT =1. 1xT =1. Anything else results in 0. So you get a series of 0 and 1 going into the MATCH function

Green: Match returns the row number of the FIRST value matching 1 (all 3 criteria met).

Blue: Index returns the corresponding value from column G.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

The formula is perfect. you have forgotten to sort the data table, as mentioned at Posts 12, 17 & 21.....

Hello Glenn,
In fear of sounding like a moron, I genuinely don't know what you mean by that. the boxes are a set size so i can't be jumbling up the different lengths, widths and depths together.
Unless you mean to just sort out the lengths in order on the storage boxes then I guess I can do that yes.

Thanks in advance,
Julian

P.S: please could you post a "sorted data table" so I actually understand what you mean.

Select the data table. data/Sort

Select data has headers
Add level/dimension 1/small to large
add level/diemnsion2/small to large
add level/dimension 3/small to large
OK.

No problem! Any time...

@Glenn

Hi! I know this has been solved. I just want to say thank you cause this formula worked perfectly for my problem, too! Thank you!!!