Results 1 to 30 of 30

Closest match with multiple criteria (3 criteria)

Threaded View

  1. #1
    Forum Contributor JulianS96's Avatar
    Join Date
    11-01-2019
    Location
    England
    MS-Off Ver
    365 (work-99%), 2016 (home-1%)
    Posts
    283

    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.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 11
    Last Post: 06-05-2023, 01:36 AM
  2. [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
  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