Array function in VBA to get last result

I have an array equation in an Excel sheet.

{=IFERROR(IF(=Sheet1!T1="approved", LOOKUP(2,1/(Sheet2!G9:G16=Sheet1!B1),Sheet2!H9:H16),""),"")}

This formula helps me get last result of the criteria instead of first.

I need it in VBA code and to assign its result to a variable called x.

My problem is with the lookup function because it is an array function.

Sub Macro2()
'
'
Name = "Ahmed Mohamed"

y = WorksheetFunction.Lookup(2, (1 / Range("B1:B3") = Name), Range("c1:c3"))
   
MsgBox (x)
   
End Sub

A VBA Lookup: Last Occurrence - XLookup and XMatch

• XLookup and XMatch have this argument search_mode which you can set to -1 to get the last occurrence.

<!-- language: lang-vb -->

Sub Lookup()

    Dim MyName As String: MyName = "Ahmed Mohamed"
    
    Dim x
       
    ' Use Worksheet.Evaluate
    
    x = ActiveSheet.Evaluate("LOOKUP(2,1/(B1:B3=""" & MyName & """),C1:C3)")
    
    If Not IsError(x) Then MsgBox x & "(Evaluate)"
    
    ' If you have Microsoft 365:
    
    ' Use 'Application.Index' and 'Application.XMatch'
    
    With Application
        x = .Index(Range("C1:C3"), .XMatch(MyName, Range("B1:B3"), , -1))
    End With
    
    If Not IsError(x) Then MsgBox x & "(Index/XMatch)"
   
    ' Use 'Application.XLookup'
    
    With Application
        x = .XLookup(MyName, Range("B1:B3"), Range("C1:C3"), , , -1)
    End With
    
    If Not IsError(x) Then MsgBox x & "(XLookup)"
   
End Sub