+ Reply to Thread
Results 1 to 10 of 10

Finding the closest match that meet multiple criteria

  1. #1
    Registered User
    Join Date
    01-20-2012
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    4

    Question Finding the closest match that meet multiple criteria

    Hello

    I am trying to find the closest match that meet multiple criteria from a set of data.

    Stock Performance Standard Deviation
    A 2 9
    AA 3 4
    AAA 3 5
    B 4 5
    BB 2 5
    BB 4 4.5
    C 3 7
    CC 2.5 1
    CCC 4 6

    I am trying to find which stock had the highest performance with the lowest volatility or closest match to the max performance but min volatility.

    I have tried to try and use the large and small formula (which is used to find the closest values) into a index match and/or sumproduct with multiple criteria.

    I have tried =index(a1:a10,match(1,large(b1:b10,countif(b2:b10,max(b2:b10))))*small(c2:c10,min(c2:c10)))),0),1)
    and sumproduct((b2:b10=large(b2:b10,countif(b2:b10,max(b2:b10))))*(c2:c10=small(c2:c10,countif(c2:c10,min(c2:c10))))*(a2:a10))

    I am not sure if this is even possible - but the above did not work. I hope this is clear but let me know if you need any further information.

    Any ideas are appreciated.

    Thank you for you help in advance.

    Edited:

    I have uploaded a file. I guess my explanation above was not great but hopefully the file will provide a better view.

    A AA AAA etc are not related, they are just random and discrete.

    Thank you
    Last edited by n_kerai; 01-25-2017 at 02:44 PM. Reason: File Upload

  2. #2
    Registered User
    Join Date
    01-20-2017
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    199

    Re: Finding the closest match that meet multiple criteria

    Why would you ask such a question? Good luck

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,977

    Re: Finding the closest match that meet multiple criteria

    The table that you posted is not very clear, so it would help if you attached a sample Excel workbook.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

    Note that the Paperclip icon does not work.

    Are the items A, AA, and AAA related, and if so please explain how.

    Hope this helps.

    Pete

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,903

    Re: Finding the closest match that meet multiple criteria

    ...and as well as uploading the workbook as Pete suggests you'll need to define your terminology.

    You can assume we know what Standard Deviation means since this is a recognised statistic, but terms like 'highest performance' and 'lowest volatility' will almost certainly mean different things to different people. So manually add some calculated results as well so that we know what you mean - and want.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Registered User
    Join Date
    01-20-2012
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Finding the closest match that meet multiple criteria

    Hi,

    Edited and updated with a file. Hope this is clearer. Thank you.

  6. #6
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,903

    Re: Finding the closest match that meet multiple criteria

    Please Login or Register  to view this content.
    Try this Array formula
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  7. #7
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,887

    Re: Finding the closest match that meet multiple criteria

    Try with in "C13"
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  8. #8
    Registered User
    Join Date
    01-20-2012
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Finding the closest match that meet multiple criteria

    Thank you both for the suggestions. Whilst they both find the min in col c based on the max in col b. I need it to reflect the stock name. Ranking is still the most preferred as it would show the top and bottom (and others in between).

    Other ideas are welcome.

  9. #9
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,903

    Re: Finding the closest match that meet multiple criteria

    Take a look at this
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    01-20-2012
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Finding the closest match that meet multiple criteria

    That worked as needed. Thank you for your help.

+ 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. Finding the closest value with multiple criteria
    By discofro2 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-28-2016, 05:21 PM
  2. Finding closest with multiple criteria
    By anwi12ad in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-04-2015, 06:42 AM
  3. [SOLVED] Multiple criteria (finding closest)
    By jram in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-20-2015, 12:02 AM
  4. [SOLVED] Find closest value to meet given criteria
    By saysys in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-12-2014, 02:11 AM
  5. [SOLVED] Closest match on Multiple Criteria
    By rbetts in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-20-2014, 08:33 AM
  6. Finding a closest match with more than one criteria
    By Jayana in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-15-2011, 06:45 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

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