Excel - Display entry with the highest value that meets a case-sensitive condition

Question

I have multiple Sheets and would now like to add a statistics sheet which only displays the entry with the highest competition results per sheet. That's easy. However I'd also like to display the highest scoring entry that meets certain genetic conditions. This is the part that gives me troubles as it has to be case sensitive, since A/A is vastly different from a/a (those are example values).

This is what I have right now:

=INDEX(Sheet1!$A$2:$A$100; MATCH(MAXIFS(Sheet1!$B$2:$B$100; Sheet1!$C$2:$C$100; "a/a"; Sheet1!$D$2:$D$100; "b/b"); Sheet1!$B$2:$B$100; 0))

Its working just fine except for the fact that it's not case sensitive and I don't know what I can do to make it case sensitive. The result should be "TestCase4" however it's "TestCase1" due to this. I already tried using CHAR() instead in a desperate attempt but that of course didn't work. And I can't get stuff like e.g. REGEXMATCH() or EXACT() to work in a MAXIFS.

I have now added a new column next to the genetics, that just writes down a 1 if it's a/a, 1.5 if it's A/a and a 0 if it's A/A and check for those columns in the MAXIFS clause but there has to be a nicer solution to this surely? I also would like to keep it somewhat biologically accurate so just changing the letters won't do.

Grateful for any tips!

Answer 1

If you have Excel 365 you can use FILTER

=TAKE(SORT(FILTER(A2:C6,EXACT("a/a",C2:C6)),2,-1),1)