Return MAX result for specific criteria for multiple values

[Melchizedek]

I would like a formula that I could lookup and return the latest date. There will be multiple matches, but only want the max one returned. I will have to do this for multiple lines and I have been unable to get Array formula's to work previously

 

[CyrusTheVirus]

Welcome to the forums. Please provide an example of what your data set looks like and your expected results.

 

[navic]

Tray, ARRAY formula

=MAX(IF($A$2:$A$9000=A2;$D$2:$D$9000))

or non-array

=AGGREGATE(14;6;$D$2:$D$9000/($A$2:$A$9000=A2);1)

But a lot of formula slows down Excel, so VBA is faster and does not slow Excel work,

Sub Macro1()
    Application.ScreenUpdating = False
    Application.CutCopyMode = True
        Range("E2").Select
        ActiveCell.FormulaR1C1 = _
            "=AGGREGATE(14,6,R2C4:R9000C4/(R2C1:R9000C1=RC[-4]),1)"
        Range("E2").Select
            Selection.AutoFill Destination:=Range("E2:E9000")
            Range("E2:E9000").Select
            Selection.Copy
        Range("E2").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
        Range("E1").Select
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
End Sub

 

[Melchizedek]

That's amazing thank you very much.

 

[Melchizedek]

Thanks Navic