+ Reply to Thread
Results 1 to 6 of 6

Closest match based on multiple criteria

  1. #1
    Registered User
    Join Date
    02-17-2014
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    3

    Closest match based on multiple criteria

    I have 2 tables:


    1 is combinations of oil and water (example). I need to match this to a container which can hold it.


    Table1
    ID Oil Water
    1 4 7
    2 1 3
    3 12 45


    in another table I have a list of containers with how much oil and water they can contain.

    Table2
    Container Name Oil Water
    Cont1 16 32
    Cont2 4 8
    Cont3 32 64
    Cont4 2 2



    I need to match each row in Table 1 with the most efficient container (should return container name) to hold the oil and water. everything will fit in Cont3, but that would leave a lot of dead space in that Container. so it needs to match to the closest possible without overflowing the container. Also each container can only hold so much water and so much oil. so Table1:id3 should only match to Cont3 because of the water requirement. make sense? think of each container as having a divider in them... so only 1 section can hold a finite amount of oil and the other a finite amount of water. one cant overflow to the other.



    Thoughts on doing this formula based?

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,105

    Re: Closest match based on multiple criteria

    Welcome to the forum! Take a look at the attachment to see if it works the way you want it to. If your first table is in A1:C4 and your second is in G1:I5, try the following in D2:

    =INDEX($G$2:$G$5,MATCH(1,($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))))*($I$2:$I$5-$C2>=0)*($H$2:$H$5-$B2>=0),0))

    This formula should be array-entered (confirm with Ctrl + Shift + Enter instead of Enter) then filled down. For your real sheet, change the ranges to suit your data. It seems to be holding up for me, but give a try to see if it delivers the right results:


    EDIT: Here's a slightly shorter version that should work just as well:

    =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))
    Attached Files Attached Files
    Last edited by CAntosh; 05-17-2017 at 12:20 PM.
    If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".

    If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
    https://www.excelforum.com/the-water...ke-thread.html

  3. #3
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Closest match based on multiple criteria

    =INDEX(tbl[[#All],[Container Name]],MIN(IF((B2<=tbl[Oil])*(C2<=tbl[Water]),ROW(tbl[Container Name])))) as array formula
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    02-17-2014
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Closest match based on multiple criteria

    This is awesome! thank you so much!

    Had to table this project for something more urgent, but this is back on my plate. helping big time!

    one question.. what the heck is happening in that IF statement? (B2<=tbl[Oil])*(C2<=tbl[Water]) 1 logical tests with an asterisk? I have been trying to google and coming up empty handed... I'm either missing something or this is some next level black magic! LOL

  5. #5
    Registered User
    Join Date
    02-17-2014
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Closest match based on multiple criteria

    Finally freaking found it!

    * = shortcut for AND!

  6. #6
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,105

    Re: Closest match based on multiple criteria

    I'm glad we could help!

    It's basically multiplication with multiple clauses, which acts like an AND. The TRUE values count as 1 and the FALSE values count as 0, so when you multiply the two clauses, only entries that return TRUE in both clauses are counted.

    Tim realized that the oil:water ratio should be pretty steady for your containers, since you're using volumes, so his solution - which requires both oil and water to be listed in ascending order - is probably best, since it's more intuitive than mine. Mine should really only be more useful if you get an oddball mixture of maximum volumes in your containers.

+ 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. Find closest time value based on multiple criteria
    By jennifers in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-30-2017, 06:25 PM
  2. [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
  3. Replies: 2
    Last Post: 12-16-2016, 07:58 AM
  4. [SOLVED] Closest match on Multiple Criteria
    By rbetts in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-20-2014, 08:33 AM
  5. [SOLVED] Multiple criteria lookup - First match :exact" and second match "Closest"
    By mweichorn in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-16-2013, 12:14 PM
  6. Replies: 6
    Last Post: 01-28-2012, 06:59 PM
  7. closest match from multiple criteria
    By wongja in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-31-2010, 01:37 AM

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