Get the respective values of the latest closing date-CodePudding

As you see on the above picture:
I need to match the values on Wb1.coumns(1) with the other workbook Wb2.coumns(1) with some conditions.
Wb2 will be filtered of the value Close at column M.
Then I seek the latest closing date and get it’s respective value at column B and input that value in Wb1.column(K).
the below code may work on the provided example correctly, But it is not reliable on my actual dataset, because it depends on the sort of many columns from oldest to newest.
This is a

I made up this in same worksheet just as explanation. The formula to get this in column K is:

=IFERROR(INDEX($N$2:$N$16,SUMPRODUCT(--($W$2:$W$16=MAX(--($Y$2:$Y$16="Close")*--($M$2:$M$16=A2)*$W$2:$W$16))*ROW($M$2:$M$16))-1),"NA")

This formula will return desired output. Applied to VBA would be:

Sub Get_Last_Closing_Date()

Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng2 As Range
Dim i As Long
Dim MyFormula As String

Application.ScreenUpdating = False

Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open("D:\Users\gaballahw\Desktop\Book2.xlsx", UpdateLinks:=False, ReadOnly:=True)

Set ws1 = wb1.Sheets(1)
Set ws2 = wb2.Sheets(1)

Set rng2 = ws2.Range("A3:M" & ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row)

With ws1
    For i = 3 To ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row Step 1
        MyFormula = "IFERROR(INDEX(" & rng2.Columns(2).Address & ",SUMPRODUCT(--(" & rng2.Columns(11).Address & _
            "=MAX(--(" & rng2.Columns(13).Address & "=""Close"")*--(" & rng2.Columns(1).Address & _
            "=" & .Range("A" & i).Value & ")*" & rng2.Columns(11).Address & "))*ROW(" & rng2.Columns(1).Address & "))-2),""NA"")" '-2 because data starts at row 3
        .Range("K" & i).Value = Evaluate(MyFormula)
    Next i
End With
 
wb2.Close SaveChanges:=False

Set rng2 = Nothing
Set ws1 = Nothing
Set ws2 = Nothing
Set wb1 = Nothing
Set wb2 = Nothing

Application.ScreenUpdating = True
End Sub

Also, if you have Excel365 you may benefit from function MAXIFS:

In Microsoft 365, you could use the following spilling formula:

=LET(sArray,Sheet2!A3:M22,sFilterCol,13,sCriteria,"Closed",SortCol1,11,SortOrder1,-1,SortCol2,1,SortOrder2,1,sLookupCol,1,sReturnCol,2,
    dLookup,Sheet1!A3:A14,dNotFound,"NA",
    sSorted,SORT(SORT(FILTER(sArray,CHOOSECOLS(sArray,sFilterCol)=sCriteria),SortCol1,SortOrder1),SortCol2,SortOrder2),
    sLookup,CHOOSECOLS(sSorted,sLookupCol),sReturn,CHOOSECOLS(sSorted,sReturnCol),
XLOOKUP(dLookup,sLookup,sReturn,dNotFound))

The 1st row holds the source constants (9) while the 2nd row the destination constants (2).
The 3rd row returns the source array filtered and sorted.
In the 4th row this modified array is used to get the source lookup and return columns.
These columns, along with the destination constants, are then fed to the XLOOKUP function in the 5th row.