计算索引值/匹配数组公式

Sub EvaluateIndexMatchFormula()

    ' Write the formula to a string first...    
    Dim Formula As String: Formula _
        = "=IFERROR(INDEX('Sheet1'!K:K," _
        & "MATCH(1,(L3='Sheet1'!G:G)*(Q3='Sheet1'!J:J),0)),"""")"
    ' ... so you can test it with:
    'Debug.Print Formula
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim dws As Worksheet: Set dws = wb.Worksheets("Sheet2") ' adjust!

    Dim drg As Range ' Destination Range

    With dws.UsedRange
        Set drg = dws.Range("P3", dws.Cells(.Rows.Count + .Row - 1, "P"))
    End With
    
    With drg
        ' Write formulae. 
        With .Cells(1)
            .FormulaArray = Formula
            .AutoFill drg, xlFillDefault
        End With
        ' Keep only values.
        .Value = .Value
    End With
 
End Sub