+ Reply to Thread
Results 1 to 3 of 3

Returning multiple results (closest match not exact) from a table using multiple criteria

  1. #1
    Registered User
    Join Date
    12-16-2016
    Location
    Dubai, UAE
    MS-Off Ver
    2016
    Posts
    2

    Returning multiple results (closest match not exact) from a table using multiple criteria

    This may be a repeated question but I failed to find a workable response anywhere.

    I have a table showing pump models, minimum & maximum flow rate, minimum & maximum head, and power in one tab called Submersible_Database (see sample attached).

    After calculating required head, flow rate and power in another tab called Sizing (see sample), I want to pull all pump models from the table (named table 7 in sample) matching the following criteria: 1- required power <= pump power 2- required head>= minimum head & <= maximum head 3- required flow>= minimum flow & <=maximum flow

    Currently, I'm using the following formula, which returns only 1st match (even when i enter it as array formula). I want all possible matches to be listed (for further refining & calculations to select the most efficient one automatically):

    [=IFERROR(INDEX(Submersible_Database!H2:H69,MATCH(1,INDEX((Submersible_Database!G2:G69>=Sizing!G43)(Submersible_Database!F2:F69<=Sizing!G43)(Submersible_Database!B2:B69<=Sizing!G48)*(Submersible_Database!C2:C69>=Sizing!G48),),FALSE)),"not in range. Change water output or head parameters")]

    Help please!

    I have a sample file on Google drive that explains it all...https://goo.gl/kLWeJV

    https://goo.gl/kLWeJV

  2. #2
    Valued Forum Contributor
    Join Date
    10-17-2010
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013, 2016, 2019, 365
    Posts
    286

    Re: Returning multiple results (closest match not exact) from a table using multiple crite

    I always prefer to work with range names and helper columns - makes it so much easier to see what's going on...

    Try the attached. It should do what you need. Uses a subset of your data.
    Attached Files Attached Files
    Kind Regards,

    Out of the Hat

    "Computers are stupid - they do EXACTLY what you tell them to"

    If I've helped you with a problem, please say thanks by clicking the small star icon on the left.

  3. #3
    Registered User
    Join Date
    12-16-2016
    Location
    Dubai, UAE
    MS-Off Ver
    2016
    Posts
    2

    Re: Returning multiple results (closest match not exact) from a table using multiple crite

    Quote Originally Posted by outofthehat View Post
    I always prefer to work with range names and helper columns - makes it so much easier to see what's going on...

    Try the attached. It should do what you need. Uses a subset of your data.
    outofthehat, you are a star! many thanks for making it so simple! I can use this approach for so many excel problems.

+ 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. Replies: 2
    Last Post: 12-18-2014, 09:52 AM
  2. [SOLVED] Closest match on Multiple Criteria
    By rbetts in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-20-2014, 08:33 AM
  3. [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
  4. Replies: 6
    Last Post: 01-28-2012, 06:59 PM
  5. Replies: 2
    Last Post: 01-28-2012, 05:26 PM
  6. Two criteria lookup - exact & closest match
    By kapucino in forum Excel General
    Replies: 3
    Last Post: 01-28-2012, 01:19 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