Multi Criterion Max If Statement

Question

My dataset looks like this...

State   Close Date  Probability      Highest Prob/State
WA       12/31/2016   50%            FALSE
WA       12/19/2016   80%            FALSE
WA       10/15/2016   80%            TRUE

My objective is to build a formula to populate the right-most column. The formula should assess Close Dates and Probabilities within each state. First, it should select the highest probability, then it should select the nearest close date if there is a tie on probability (as in the example). For that record, it should read "TRUE".

I assume this would include a MAX IF statement but haven't been able to get it to work.

Here is a more robust set of data I'm working with. It may actually be easier to first find the highest probability within each Region then select the minimum (oldest) date if there is a tie on probability. This too will serve my purposes.

Region                    Forecast Close Date   Probability (%)
Okeechobee FL             6/27/2016                 90
Okeechobee West FL        7/1/2016                  40
Albany GA                 3/11/2016                 100
Emerald Coast FL          6/30/2016                 60
Emerald Coast FL          10/1/2016                 40
Cullman_Hartselle TN      4/30/2016                 10
North MS                  10/1/2016                 25
Roanoke VA                8/31/2016                 25
Roanoke VA                8/1/2016                  40
Gardena CA                6/1/2016                  80
Gardena CA                6/1/2016                  80
Lomita-Harbor City        6/30/2016                 60
Lomita-Harbor City        6/30/2016                 0
Lomita-Harbor City        6/30/2016                 40
Eastern NC                6/30/2016                 60
Northwest NC              9/16/2016                 10
Fort Collins_Greeley CO   3/1/2016                  100
Northwest OK              6/30/2016                 100
Southwest MO              7/29/2016                 90
Northern NH-VT            3/1/2016                  20
South DE                  12/1/2016                 0
South DE                  12/1/2016                 20
Kingston NY               12/30/2016                5
Longview WA               11/30/2016                5
North DE                  12/1/2016                 20
North DE                  12/1/2016                 0
Salt Lake City UT         8/31/2016                 20
Idaho Panhandle           8/26/2016                 0
Bridgeton_Salem NJ        7/1/2016                  25
Bridgeton_Salem NJ        7/1/2016                  65
Layton_Ogden UT           3/25/2016                 5
Central OR                6/30/2016                 10

Answer 1

I think this is OK now but needs to be checked against the more complete set of data provided by OP.

It counts:-

(1) Any rows with same state but higher probability

(2) Any rows with same state and probability, in the future (or present) and nearer to today's date

(3) Any rows with same state and probability, in the past and nearer to today's date.

If all these are zero, you should have the right one.

=COUNTIFS($A$2:$A$100,$A2,$C$2:$C$100,">"&$C2)
+COUNTIFS($A$2:$A$100,$A2,$C$2:$C$100,$C2,$B$2:$B$100,"<"&$G$2+IF ($B2>=$G$2,DATEDIF($G$2,$B2,"d"),DATEDIF($B2,$G$2,"d")),$B$2:$B$100,">="&$G$2)
+COUNTIFS($A$2:$A$100,$A2,$C$2:$C$100,$C2,$B$2:$B$100,">"&$G$2-IF($B2>=$G$2,DATEDIF($G$2,$B2,"d"),DATEDIF($B2,$G$2,"d")),$B$2:$B$100,"<"&$G$2)
=0

If the dates are all in the future, it can be simplified a lot:-

=COUNTIFS($A$2:$A$100,$A2,$C$2:$C$100,">"&$C2)
+COUNTIFS($A$2:$A$100,$A2,$C$2:$C$100,$C2,$B$2:$B$100,"<"&$G$2+DATEDIF($G$2,$B2,"d"))
=0

Answer 2

The following Array formula should work:

=(ABS(B2-$F$2)=MIN(IF(($A$2:$A$33=A2)*(C2=MAX(IF($A$2:$A$33=A2,$C$2:$C$33))),ABS($B$2:$B$33-$F$2))))*(C2=MAX(IF($A$2:$A$33=A2,$C$2:$C$33)))>0

Being an array formula use Ctrl-Shift-Enter when exiting Edit mode. If done properly Excel will put {} around the formula.

Edit

Added @tigeravatar suggestion to avoid volatile functions.