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

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

Please Login or Register  to view this content.

Try this Array formula

Try with in "C13"

Formula:

Please Login or Register  to view this content.

Take a look at this