Index and Match Matrix formula to-CodePudding

I'm really stumped with converting Index Match to VBA. I'm very new to VBA, this is proving to be beyond my abilities.

I have a table in Sheet2 With Columns, 'Case', 'Probability', 'Impact' & 'Severity'. Then a Matrix in Sheet1

My formula (filled down the column) is:

=IFNA(INDEX(Sheet1!$C$2:$G$6,MATCH([@Probability],Sheet1!$B$2:$B$6,0),MATCH([@Impact],Sheet1!$C$1:$G$1,0)),"")

I'm trying to auto-populate 'Severity' in the table based on the values in the Matrix

Table

Matrix

I tried using Application.WorksheetFunction but I don't get any results.

Any advice would be much appreciated.

CodePudding user response：

VBA Using `INDEX/MATCH` Formula

These will populate the values instead of the formulas.
If you remove the line .Value = .Value, the formulas stay.
Adjust the worksheet and table names.

Option Explicit

Sub TestEdu()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet2") ' adjust
    Dim tbl As ListObject: Set tbl = ws.ListObjects("Table1") ' adjust
    Dim lcl As ListColumn: Set lcl = tbl.ListColumns("Severity")
    
    With lcl.DataBodyRange
        .Formula = "=IFNA(INDEX(Sheet1!$C$2:$G$6,MATCH([@Probability]," _
            & "Sheet1!$B$2:$B$6,0),MATCH([@Impact],Sheet1!$C$1:$G$1,0)),"""")"
        .Value = .Value
    End With

End Sub

Sub TestCompact()
    With ThisWorkbook.Worksheets("Sheet2").ListObjects("Table1") _
            .ListColumns("Severity").DataBodyRange
        .Formula = "=IFNA(INDEX(Sheet1!$C$2:$G$6,MATCH([@Probability]," _
            & "Sheet1!$B$2:$B$6,0),MATCH([@Impact],Sheet1!$C$1:$G$1,0)),"""")"
        .Value = .Value
    End With
End Sub

VBA Using INDEX/MATCH Formula

VBA Using `INDEX/MATCH` Formula