+ Reply to Thread
Results 1 to 30 of 30

Closest match with multiple criteria (3 criteria)

  1. #1
    Forum Contributor JulianS96's Avatar
    Join Date
    11-01-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    192

    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
    Last edited by JulianS96; 11-01-2019 at 08:24 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,872

    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.

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


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Contributor JulianS96's Avatar
    Join Date
    11-01-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    192

    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.
    Thanks in advance!

    Julian
    Last edited by JulianS96; 11-01-2019 at 08:20 AM.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,872

    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. #5
    Forum Contributor JulianS96's Avatar
    Join Date
    11-01-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    192

    Re: Closest match with multiple criteria (3 criteria)

    That's what I meant sorry
    Attached Files Attached Files

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,872

    Re: Closest match with multiple criteria (3 criteria)

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

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,839

    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))
    Attached Files Attached Files
    Glenn



  8. #8
    Forum Contributor JulianS96's Avatar
    Join Date
    11-01-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    192

    Re: Closest match with multiple criteria (3 criteria)

    Hello Ali,

    Please find attached the updated workbook.

    Kind Regards,

    Julian
    Attached Files Attached Files

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,872

    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. #10
    Forum Contributor JulianS96's Avatar
    Join Date
    11-01-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    192

    Re: Closest match with multiple criteria (3 criteria)

    Quote Originally Posted by Glenn Kennedy View Post
    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. #11
    Forum Contributor JulianS96's Avatar
    Join Date
    11-01-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    192

    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
    Attached Files Attached Files
    Last edited by JulianS96; 11-01-2019 at 08:51 AM.

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,839

    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.

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

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,872

    Re: Closest match with multiple criteria (3 criteria)

    Isn't your expected answer for the 3rd one incorrect?
    I agree - see post #9.

  14. #14
    Forum Contributor JulianS96's Avatar
    Join Date
    11-01-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    192

    Re: Closest match with multiple criteria (3 criteria)

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

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,839

    Re: Closest match with multiple criteria (3 criteria)

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

  16. #16
    Forum Contributor JulianS96's Avatar
    Join Date
    11-01-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    192

    Re: Closest match with multiple criteria (3 criteria)

    Quote Originally Posted by JulianS96 View Post
    updated!
    Please find attached new version.

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

    Thanks in advance,

    Julian
    Last edited by JulianS96; 11-01-2019 at 09:01 AM.

  17. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,839

    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. #18
    Forum Contributor JulianS96's Avatar
    Join Date
    11-01-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    192

    Thumbs up 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!
    Last edited by AliGW; 11-01-2019 at 09:15 AM. Reason: Please don't quote unnecessarily!

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,872

    Re: Closest match with multiple criteria (3 criteria)

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

  20. #20
    Forum Contributor JulianS96's Avatar
    Join Date
    11-01-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    192

    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. #21
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,839

    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.



    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.

  22. #22
    Forum Contributor JulianS96's Avatar
    Join Date
    11-01-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    192

    Re: Closest match with multiple criteria (3 criteria)

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

  23. #23
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,839

    Re: Closest match with multiple criteria (3 criteria)

    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.

  24. #24
    Forum Contributor JulianS96's Avatar
    Join Date
    11-01-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    192

    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

    Thanks in advance,

    Julian
    Attached Files Attached Files

  25. #25
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,839

    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. #26
    Forum Contributor JulianS96's Avatar
    Join Date
    11-01-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    192

    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.

    Thanks in advance,
    Julian

    P.S: please could you post a "sorted data table" so I actually understand what you mean.
    Last edited by JulianS96; 11-04-2019 at 12:17 PM.

  27. #27
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,839

    Re: Closest match with multiple criteria (3 criteria)

    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.
    Attached Images Attached Images
    Attached Files Attached Files

  28. #28
    Forum Contributor JulianS96's Avatar
    Join Date
    11-01-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    192

    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. #29
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,839

    Re: Closest match with multiple criteria (3 criteria)

    No problem! Any time...

  30. #30
    Registered User
    Join Date
    01-31-2021
    Location
    Philippines
    MS-Off Ver
    365
    Posts
    4

    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!!!
    Last edited by AliGW; 02-03-2021 at 04:12 AM. Reason: PLEASE don't quote unnecessarily!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Match by multiple criteria and closest value
    By Benta1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-17-2019, 04:53 AM
  2. Replies: 10
    Last Post: 07-20-2017, 12:16 PM
  3. Closest match based on multiple criteria
    By jeffs24 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-30-2017, 04:11 PM
  4. [SOLVED] Finding the closest match that meet multiple criteria
    By n_kerai in forum Excel General
    Replies: 9
    Last Post: 01-26-2017, 12:15 PM
  5. Replies: 2
    Last Post: 12-16-2016, 07:58 AM
  6. [SOLVED] Closest match on Multiple Criteria
    By rbetts in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-20-2014, 08:33 AM
  7. closest match from multiple criteria
    By wongja in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-31-2010, 01:37 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1