+ Reply to Thread
Results 1 to 4 of 4

INDEX MATCH from table with multiple MATCH criteria

  1. #1
    Forum Contributor
    Join Date
    01-12-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    151

    INDEX MATCH from table with multiple MATCH criteria

    Hi Guys,

    Back again. Today I'm struggling a bit with data I'm trying to extract from a table with multiple MATCH criteria. File attached.

    I need to determine what diameter pipe is needed based on the following factors.

    The total LU (cell J24) must be less or equal than the max load (row 11)
    The pipe length (H24) must be less or equal than the max length in row 14 (or ignored if no value in 14 is shown)
    The highest single LU (F24) must not be less or equal than the highest load (row 12)
    The diameter (M24) is then matched to the Diameter in (row 13)

    Hope you can help and appreciate the input in advance
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,631

    Re: INDEX MATCH from table with multiple MATCH criteria

    You need to provide the expected result, with reasons!

    I am not sure that one exists in your sample! Which makes it difficult to solve

    how do you match the diameter in row 13, there is not an exact match

  3. #3
    Forum Contributor
    Join Date
    01-12-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    151

    Re: INDEX MATCH from table with multiple MATCH criteria

    Hi Davsth,

    Thanks for the reply.

    Sorry, my bad, the values in the example should have been an expected result but I now see it is not.

    In the example the total LU (J24) is 47, so immediately the expected Diameter is 28mm.
    The length is 13 but 28mm has no Max length so it can be ignored.
    The Highest LU (F24) is 13, but 28mm also has no highest value LU so can be ignored.

    I have attached a better example and will add a description now.

    The Total LU is 6 (J24)
    The Length is 7 (H24)
    The Highest LU is 6 (F24)

    Total LU of 6 would indicate that 15mm is sufficient
    Length of 7 is equal or lower than the max length of 7 for 15mm
    Highest LU of 6 is higher than Highest value allowed of 4 however, so the pipe size would have to be 22mm in this case.

    I've made a few more changes to make things easer to understand, apologises. This is straight out of a British Standard so clear as mud as always

    Thanks for the input
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,631

    Re: INDEX MATCH from table with multiple MATCH criteria

    Your logic and explanations still do not reconcile or at least are unclear

    Total LU of 6 would indicate that 15mm is sufficient why I can see that 6 corresponds to a diameter of 15 in column I, however columns D to I all satisify this conditions, do you mean closest to < or equal
    Length of 7 is equal or lower than the max length of 7 for 15mm true but also for e and also f if it is just less than
    Highest LU of 6 is higher than Highest value allowed of 4 however, so the pipe size would have to be 22mm in this case. well yes 22

    what does < or ignored actually mean?

    A guess is entered as an array with shift control enter

    =MIN(MIN(IF((H24<=D14:O14)*(F24<=D12:O12)*(J24<=O11:O11)>0,D13:O13,99999)),MIN(IF((D14:O14="")*(F24<=D12:O12)*(J24<=O11:O11)>0,D13:O13,99999)))

    or in case it cann't be solved

    =IF(MIN(MIN(IF((H24<=D14:O14)*(F24<=D12:O12)*(J24<=O11:O11)>0,D13:O13,9999)),MIN(IF((D14:O14="")*(F24<=D12:O12)*(J24<=O11:O11)>0,D13:O13,99999)))=9999,"No solution",MIN(MIN(IF((H24<=D14:O14)*(F24<=D12:O12)*(J24<=O11:O11)>0,D13:O13,9999)),MIN(IF((D14:O14="")*(F24<=D12:O12)*(J24<=O11:O11)>0,D13:O13,99999))))

    9999 is my unsolvable answer, so i recode to no solution

+ 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] Index/Match when the Match criteria is based on a second table
    By Plummet in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-10-2017, 01:02 PM
  2. Need help in Index, Match usage to match multiple criteria in sum function
    By Summer0830 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-26-2017, 02:47 AM
  3. Index-Match Multiple Criteria From Same Table
    By mycon73 in forum Excel General
    Replies: 0
    Last Post: 04-27-2016, 06:54 PM
  4. Index/Match to Match entries on multiple criteria
    By manning457 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-06-2015, 02:32 PM
  5. [SOLVED] Index Match with multiple criteria and selecting data from a large table
    By Aquarock in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-14-2014, 09:26 PM
  6. Replies: 2
    Last Post: 09-27-2014, 04:34 PM
  7. [SOLVED] MATCH 3 criteria in a table with INDEX & MATCH
    By SteelMaster in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-17-2014, 04:04 PM

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