Excel - Selecting Only One Max Value

Question

I have a dataset that looks like below. Column E would be my formula column. I would like Column E to read "Max" for the row where the ID contains the max probability for that state. If two IDs for the same State have the same probability, as in Maryland, I want "Max" to show for the ID with the nearest Date. If two IDs match on both Probability and Date, then I want "Max" to appear for only one ID within each group of States.

    Column A       Column B     Column C    Column D    Column E
    State        Probability       Date        ID       Formula Field
    California        10%       12/31/2016    123   
    California        20%       1/7/2017      129   
    California        23%       1/14/2017     135       Max
    Colorado          26%       1/21/2017     141   
    Colorado          38%       12/31/2016    147       Max
    Illinois          44%       1/14/2017     153       Max
    Illinois          44%       1/14/2017     159   
    Illinois          20%       1/21/2017     165   
    Illinois          35%       1/28/2017     171   
    Maryland          41%       2/4/2017      177   
    Maryland          41%       12/31/2016    183       Max
    Maryland          35%       1/7/2017      189   
    Michigan          20%       1/14/2017     195   
    Michigan          35%       1/21/2017     201   
    Michigan          38%       1/28/2017     207       Max
    West Virginia     41%       2/4/2017      213   
    West Virginia     44%       2/11/2017     219       Max

Answer 1

Here is the new formula:

=IF(D2=MIN(IF((ABS($C$2:$C$18-$H$1)=MIN(IF(($A$2:$A$18=A2)*($B$2:$B$18=MAX(IF(($A$2:$A$18=A2),$B$2:$B$18))),ABS($C$2:$C$18-$H$1))))*($A$2:$A$18=A2),$D$2:$D$18)),"Max","")

This is an array formula. Being an array it must be confirmed with Ctrl-Shift-Enter. If done properly excel will put {} around the formula.

As per your comments the following formulas will work when IDs are text.

To get the first row where the criteria match, when there are duplicates:

=IF(ROW(D2)=MIN(IF((ABS($C$2:$C$18-$H$1)=MIN(IF(($A$2:$A$18=A2)*($B$2:$B$18=MAX(IF(($A$2:$A$18=A2),$B$2:$B$18))),ABS($C$2:$C$18-$H$1))))*($A$2:$A$18=A2),ROW($D$2:$D$18))),"Max","")

To put "Max" in both when there is a tie:

=IF((ABS(C2-$H$1)=MIN(IF(($A$2:$A$18=A2)*($B$2:$B$18=MAX(IF(($A$2:$A$18=A2),$B$2:$B$18))),ABS($C$2:$C$18-$H$1))))*($A$2:$A$18=$A$2:$A$18),"Max","")

Answer 2

Using the COUNTIFS approach used in my previous answer,

=IF(COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,">"&$B2)
+COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2,$C$2:$C$100,"<"&$G$2+DATEDIF($G$2,$C2,"d"))
+COUNTIFS($A$1:$A1,$A2,$E$1:$E1,"MAX"),
"","MAX")

Answer 3

This might help but its a little cumbersome. The return of the function apparently cannot be blank so it will have a 0 in all the other rows. But if you use this function your range must be selected the same for every group. ** Also the function would have to be in the column right next to it or you would have to edit the function in the offset by however many columns you want to look at.

Link to Excel sheet screenshot

Function FindMaxByState(rng As Range)
Dim cell As Range
Dim value As Double

value = 0

For Each cell In rng
If IsNumeric(cell.value) Then
    If cell.value > value Then
        value = cell.value
    End If   
End If

Next cell

If ActiveCell.Offset(0, -1).value = value Then
    FindMaxByState = "Max"
Else
    FindMaxByState = 0
End If

End Function