# Closest match based on multiple criteria

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

3. ## 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

4. ## 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. ## Re: Closest match based on multiple criteria

Finally freaking found it!

* = shortcut for AND!

6. ## 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.

##### Users Browsing this Thread

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

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