On Aug 19, 9:40*pm, smartin wrote:
Max wrote:
On the face of it, think you could try an index n match,
like this, array-entered*, then copied down:
=INDEX(F$2:F$5050,MATCH(MAX(IF(A$2:A$5050=A3,C$2:C $5050)),IF(A$2:A$5050=A3,*C$2:C$5050),0))

*press CTRL+SHIFT+ENTER to confirm the formula

And if you need to include an additional criteria based on what's in col D,
eg: Actual, then try something like this, array-entered:
=INDEX(F$2:F$5050,MATCH(MAX(IF((A$2:A$5050=A3)*(D$ 2:D$5050="Actual"),C$2:C$*5050)),IF((A$2:A$5050=A3 )*(D$2:D$5050="Actual"),C$2:C$5050),0))

Likewise for "Estimated"

Aha, following the formula auditor at last I see what I was doing wrong.

For "Actual":
=INDEX(C$2:C$5050,MATCH(MAX(IF((A$2:A$5050=A3)*(D$ 2:D$5050="Actual"),C$2:C$*5050)),IF((A$2:A$5050=A3 )*(D$2:D$5050="Actual"),C$2:C$5050),0))
* * * * ^ * ^

And, as you say, likewise for "Estimated"

Max and Smartin,
Thanks you both for all your help. I was able to get it working just
need to make sure the data is sorted in ascending order, but, smartin
I think your “=INDEX(C$2:C$5050,MATCH(MAX(IF((A$2:A$5050=A3)*(D $2:D
$5050="Actual"),C$2:C$5050)),IF((A$2:A$5050=A3)*(D $2:D$5050="Actual"),C
$2:C$5050),0))” will help with that it keeps returning #N/A.

When I have some more time I will look into some more, but, thanks you
again…