# Closest match with multiple criteria (3 criteria)

1. ## 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))

Julian

2. ## Re: Closest match with multiple criteria (3 criteria)

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.

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.

3. ## Re: Closest match with multiple criteria (3 criteria)

Hello Ali!

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

Julian

4. ## Re: Closest match with multiple criteria (3 criteria)

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

5. ## Re: Closest match with multiple criteria (3 criteria)

That's what I meant sorry

6. ## Re: Closest match with multiple criteria (3 criteria)

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

7. ## Re: Closest match with multiple criteria (3 criteria)

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

8. ## Re: Closest match with multiple criteria (3 criteria)

Hello Ali,

Please find attached the updated workbook.

Kind Regards,

Julian

9. ## Re: Closest match with multiple criteria (3 criteria)

Should this be Cont2 and Cont4?

Excel 2016 (Windows) 32 bit
B
C
D
24
Depth 119 matches: Cont2
25
Cont3
 Sheet: Sheet1

10. ## Re: Closest match with multiple criteria (3 criteria)

Originally Posted by Glenn Kennedy
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))
Oh my god. Amazing. I have been struggling on this for days haha.

Would you mind telling me how that formula works?
No problem if not, i'm happy to take the solution!

CHeers again!
Julian

11. ## Re: Closest match with multiple criteria (3 criteria)

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

12. ## Re: Closest match with multiple criteria (3 criteria)

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

13. ## Re: Closest match with multiple criteria (3 criteria)

I agree - see post #9.

14. ## Re: Closest match with multiple criteria (3 criteria)

Originally Posted by AliGW
I agree - see post #9.
updated!

15. ## Re: Closest match with multiple criteria (3 criteria)

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

16. ## Re: Closest match with multiple criteria (3 criteria)

Originally Posted by JulianS96
updated!

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

Julian

17. ## Re: Closest match with multiple criteria (3 criteria)

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.

18. ## Re: Closest match with multiple criteria (3 criteria)

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!

20. ## Re: Closest match with multiple criteria (3 criteria)

Whenever you're available, and whether you want to or not, but would you be so kind as to explain how the formula works?
I don't understand the multiplication bits either * I think i understand some of it, but I didn't know you could index an index too!

Anyway, thanks again
Kind Regards,
Julian
P.S: I will post things right next time haha!

21. ## Re: Closest match with multiple criteria (3 criteria)

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

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.

22. ## Re: Closest match with multiple criteria (3 criteria)

Amazing! Thank you Glenn!!! That'll be useful for future reference

23. ## Re: Closest match with multiple criteria (3 criteria)

You're welcome.

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.

24. ## Re: Closest match with multiple criteria (3 criteria)

Hello Excel forum!

I previously posted a problem on trying to make a closest match formula with 3 different criteria work.

That problem was fortunately solved..... until now!

I am working on some box sizes and want the formula to automatically select the box with the closet match to the dimensions given of e.g. BOX "C1": 600x400x420 shuld be closely matched with a box of e.g. 600x410x325 and not a box of 600x411x350.
My actual problem is that the formula is matching with a box of 601.98x401.32x320.04 rather than a box of 600x400x320.

I will post the sample spreadsheet below with a mockup of the desired answer

Julian

25. ## Re: Closest match with multiple criteria (3 criteria)

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

26. ## Re: Closest match with multiple criteria (3 criteria)

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.

Julian

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

27. ## Re: Closest match with multiple criteria (3 criteria)

Select the data table. data/Sort

OK.

28. ## Re: Closest match with multiple criteria (3 criteria)

Thank you very much! I finally understand what you mean and it is very easy to sort the data too.

Thanks again! SORTED & SOLVED!

29. ## Re: Closest match with multiple criteria (3 criteria)

No problem! Any time...

30. ## Re: Closest match with multiple criteria (3 criteria)

@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!!!

There are currently 1 users browsing this thread. (0 members and 1 guests)