# Finding the closest match that meet multiple criteria

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

2. ## Re: Finding the closest match that meet multiple criteria

Why would you ask such a question? Good luck

3. ## 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. ## Re: Finding the closest match that meet multiple criteria

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.

5. ## Re: Finding the closest match that meet multiple criteria

Hi,

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

6. ## Re: Finding the closest match that meet multiple criteria

``Please Login or Register  to view this content.``
Try this Array formula

7. ## Re: Finding the closest match that meet multiple criteria

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

8. ## 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. ## Re: Finding the closest match that meet multiple criteria

Take a look at this

10. ## Re: Finding the closest match that meet multiple criteria

That worked as needed. Thank you for your help.

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